Thread: How I can check a substring is a valid number in postgresql ?

<span class="HcCDpe">Dear All,<br /><br />     How I can check a substring is a valid number in postgresql ?<br /><br
/><br/> example:<br /><br /> I have a query <br /><br /> select max(substring(code,2,length(code))::INT) from emp where
substring(code,0,2)= 'A'; <br /><br /><br /><br /> code <br /> ========<br /> A0001<br /> A0002<br /> ABC005<br />
S0002<br/><br /> This query fails because of </span><span class="HcCDpe">ABC002, how I can avoid this error , if there
isa any way to check </span><span class="HcCDpe">substring(code,1,length(code))  is a number ,then i can correct the
queryby put it in where condition.<br /><br /> I want to get the result as 2(ie want to avoid checking ABC005<br /><br
/>is there is any built in function to check the substring is a number in postgresql?<br /><br /> Iam using  postgres
8.1.8<br /><br />  pls help me <br /><br /><br /> thanks in advance:<br /><br /> Anoop</span> 

On Thu, 24 Apr 2008, "Anoop G" <anoopmadavoor@gmail.com> writes:
> How I can check a substring is a valid number in postgresql ?

SELECT col ~ '[0-9]+';


Regards.

This regular expression works for numeric/float values as
well as integers.  It allows for exponents.

tvalue ~ E'^[+-]?[0-9]+(\\\\.[0-9]*)?([Ee][+-]?[0-9]+)?\$'

TJ O'Donnell
http://www.gnova.com/