[SOLVED] validate integer value in query

[o3d]

Good day,

 

I need help to determine if a field in my where clause is in fact a valid integer value.

 

I don't know how to do exception handling in a query so any help would be appreciated.

 

I suppose the query could look something like:

 

select cast(field2 as decimal) as field2

from table1

where is_number(field2) = 't'

[the_oliver]

Not sure if i understood that but perhaps something simple like:

 

SELECT cast(field2 as decimal) as field2 FROM table1 WHERE field2 > 0

[o3d]

Thanks for the reply, but when you see if a value is > 0, I assume postgres also checks that it is not null. So nope, didn't work.

 

--query

select cast(a.field1 as decimal)

from (

select '1' as field1 union

select 'two' union

select '3'

) as a

where

a.field1 > 0

 

--result

ERROR: invalid input syntax for type numeric: "two"

SQL state: 22P02

[o3d]

I found a solution, don't know why i haven't looked at this before. I used a plain simple regular expression to determine if the value contains numeric values:

 

select cast(a.field1 as decimal)

from (

  select '1' as field1 union

  select 'two' union

  select '3'

) as a

where

  a.field1 ~ '^\\d+$'

 

This query will only return fields which contains numeric values.

 

RESULT

?column?

--------

1

3