Thread: Function testing if a string is a number ?

Hi list !

I am searching for a function that would return true if a string
is a number.
In Access I would have written IsNumeric('mystring'), but this
function does not exist in postgres.
Maybe I am doing this the wrong way actually : I want to fill an
integer field with the values from a varchard field. Some of the
values are not convertible though, so just executing :
UPDATE thetable
SET numfield=varcharfield::int4;
returns an error 'Invalid input syntax for integer "blablabla"'.
My idea was to run :
UPDATE thetable
SET numfield=varcharfield::int4
WHERE IsNumeric(varcharfield);
but maybe there is a better way to achieve this ?

Thanks for your help !

Regards
--
Arnaud

Arnaud Lesauvage <thewild@freesurf.fr> writes:
> I am searching for a function that would return true if a string
> is a number.
> In Access I would have written IsNumeric('mystring'), but this
> function does not exist in postgres.

Write your own using a pattern test, for instance
    varchar ~ '^[0-9]+$'
See
http://www.postgresql.org/docs/8.1/static/functions-matching.html

            regards, tom lane

Tom Lane a écrit :
> Arnaud Lesauvage <thewild@freesurf.fr> writes:
>> I am searching for a function that would return true if a string
>> is a number.
>> In Access I would have written IsNumeric('mystring'), but this
>> function does not exist in postgres.
>
> Write your own using a pattern test, for instance
>     varchar ~ '^[0-9]+$'
> See
> http://www.postgresql.org/docs/8.1/static/functions-matching.html

Oh yes ! Regular expressions !
I have to think about it now !!!

Thanks a lot Tom !

Regards
--
Arnaud