how to select non 0 and numeric values from Varchar field

Database
Enthusiast

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.

1 REPLY
Senior Apprentice

Re: how to select non 0 and numeric values from Varchar field

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 :-)