Thread: isnumeric - checking if text variable is convertable to numeric
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. Something like: IF isnumeric(text_var) Then num_var:=Cast (text_var AS numeric); ELSE .... END IF; Thanks for the help. Balázs
Re: isnumeric - checking if text variable is convertable to numeric
From
Martijn van Oosterhout
Date:
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.
Attachment
I was trying to actually do a cast and trap the error but (beside not having succeeded in doing so) this is a much nicer way. Thanks. Balázs
On 24 Apr 2006 12:49:51 -0700, SunWuKung <Balazs.Klein@t-online.hu> wrote: > I was trying to actually do a cast and trap the error but (beside not > having succeeded in doing so) this is a much nicer way. > > Thanks. > Balázs > > in 8.1 you can use exception blocks BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END; http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- regards, Jaime Casanova "What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast." Randal L. Schwartz
I tried this but couldn't find out what would be the WHEN condition for Select Cast('asdf' as numeric) ERROR: invalid input syntax for type numeric regards, Balázs
am 25.04.2006, um 2:01:49 -0700 mailte SunWuKung folgendes: > I tried this but couldn't find out what would be the WHEN condition for > > Select Cast('asdf' as numeric) > ERROR: invalid input syntax for type numeric create or replace function check_numeric(varchar) returns bool as $$ declare i numeric; begin i := $1::numeric; return 't'::bool; EXCEPTION WHEN invalid_text_representation then return 'f'::bool; end; $$ language plpgsql immutable strict; HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===