Thread: SQL state: 22P02

<small><font face="Courier New">Hi,<br /><br /> I got the message "SQL state: 22P02" <br /><br /><br /><br /><b>to
produce:</b><br/><br /> create table test (num varchar(20));<br /><br /> insert into test (num) values (null);<br />
select* from test where cast(num as int8) = 0;   --ok, no error<br /> insert into test (num) values ('123123');<br />
select* from test where cast(num as int8) = 123123;   --ok, no error<br /><br /> insert into test (num) values
('                   ');<br /> select * from test where cast(num as int8) = 123123;  -- error<br /> select * from test
wherecase when trim(num) <> '' then cast(num as int8) = 123123 else false end; -- ok, changed query and no
error<br/><br /> insert into test (num) values ('a');<br /> select * from test where cast(num as int8) = 123123;  --
error<br/> select * from test where case when trim(num) <> '' then cast(num as int8) = 123123 else false end;  --
error<br/><br /> I need get all tuples was the table have converting the characters to number. If one conversion fail
therenot displayed. <br /><br /><b>Ex:</b><br />   num      return<br />   ----     ------<br />   0         0<br />  
null     false<br />   12        12<br />   a         false<br />   12ab      false<br /></font></small><font
face="CourierNew"><small><br /> it´s possible get these results ?<br /><br /><br /> other question:<br /><br /> Why
thismessage ("</small></font><small><font face="Courier New">SQL state: 22P02</font></small><font face="Courier
New"><small>")is not displayed with the SQL command on log in data/pg_log ?<br /> only appears     <br /><br />
2007-11-2212:55:13 BRT ERROR:  invalid input syntax for integer: "a"<br /><br /><br /> thanks,<br /><br /> Franklin
Haut<br/><br /><br /><br /><br /><br /></small></font> 

On Nov 22, 2007 11:24 AM, Franklin Haut <franklin.haut@gmail.com> wrote:
>    num      return
>    ----     ------
>    0         0
>    null      false
>    12        12
>    a         false
>    12ab      false
>
>  it´s possible get these results ?

Try:

SELECT   NUM , CASE     WHEN TRIM(NUM) ~ '^[0-9]+(.[0-9]+){0,1}$'     THEN NUM     ELSE 'false'   END AS RETURN
FROM TEST;