Re: isnumeric - checking if text variable is convertable to numeric - Mailing list pgsql-general

On Mon, Apr 24, 2006 at 05:31:46AM -0700, SunWuKung wrote:
> I am writing a pgsql function and I would need to create a condition
> based on the value of a text variable. I would need to check if
> text_var is convertable to numeric type and if yes do the conversion,
> if no do something else. I couldn't find out how to do that.

I'd use pattern matching:

test=# select a, textregexeq(a,'^[[:digit:]]+(\.[[:digit:]]+)?$') as
isnumeric from test;
   a    | isnumeric
--------+-----------
 string | f
 100.   | f
 100    | t
 100.04 | t
 .04    | f
 0.04   | t
 0.04d  | f
(7 rows)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.