All Forums Database
terankit 77 posts Joined 03/12
04 Jul 2014
how to select non 0 and numeric values from Varchar field

Hi,
 
We have an ID field in a table. Currently this contains values such as:
????
xxxx
0
00
0000
123
0123
 
I want to select only 123, 0123. Currently I am doing sel * from table where column like any ('%1%','%2%'...'%9%').
Is there any other way to do it.
 
Thanks.

dnoeth 4628 posts Joined 11/04
04 Jul 2014

If you're on TD14 you might use a Regular Expression.
This is exactly hat you're doing right now, "check if there's any digit between 1 and 9":

where regexp_similar(id,'[1-9]+') = 1

But this will return also '??1?', if you don't want that:

where regexp_similar(id,'0*+[0-9]+') = 1

"strip of any leading zero and then check for digits only"

 

And now start cleaning your data :-)
 

Dieter

You must sign in to leave a comment.