Skip site navigation (1) Skip section navigation (2)

Function testing if a string is a number ?

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-novice(at)postgresql(dot)org
Subject: Function testing if a string is a number ?
Date: 2006-02-20 14:14:51
Message-ID: 43F9CEDB.8000605@freesurf.fr (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-02-20 15:09:46
Subject: Re: Function testing if a string is a number ?
Previous:From: Bruno Wolff IIIDate: 2006-02-20 14:12:19
Subject: Re: Restoring SQL Databse for use in PostgreSQL.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group