atoi-like function: is there a better way to do this? - Mailing list pgsql-general

One of our tables has a few columns that may be interpreted as strings
or may be numbers (data type is varchar, numbers are stored as
decimal). Generally, operations are performed on the string, but
sometimes we need to parse out a number - without it failing on error.
I wrote the following function to approximate to the semantics of
atoi:

create or replace function str2int(val varchar) returns bigint immutable as $$
begin
    val=substring(val from '[0-9]*');
    if length(val) between 1 and 19 then return val::bigint; end if;
    return 0;
end;
$$ language plpgsql;

It uses a regular expression to chop the string down to just the
leading digits (leading only - '123.45' should parse as 123). Is there
a more efficient way to achieve this?

Thanks!

Chris Angelico