atoi-like function: is there a better way to do this?

From: Chris Angelico <rosuav(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: atoi-like function: is there a better way to do this?
Date: 2012-03-05 02:13:14
Message-ID: CAPTjJmprw6m+LdeEc+49cysW=T_cqiGDbuYMULfqyzKBDysK7w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Angelico 2012-03-05 03:31:30 Re: atoi-like function: is there a better way to do this?
Previous Message Rory Campbell-Lange 2012-03-04 20:58:09 Re: Return unknown resultset from a function