CODEWITHSUNDEEP
sqloracle-databasecastingnumbers
ifuwannaride
ifuwannaride

Reputation: 121

Where clause by only number-convertable rows

I have a table with 2 columns like this

COLNUM | COLSTR
---------------
1      | 001223
2      | 002234
3      | ds2-dd
4      | 003344

I would like to make query like this

SELECT * FROM TABLE WHERE CAST(COLSTR AS NUMBER) IN (1223,3344)

But since 4th row can not cast to number, query fails. I could create a view which would eliminate non-number-transformable rows, but I would like to make this with one query. How do I do it?

Oracle 11g.

Thanks.

Upvotes: 0

Views: 54

Answers (1)

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

I would suggest to use regular expression

SELECT * FROM TABLE WHERE REGEXP_LIKE(COLSTR,'^[0-9]$')

Upvotes: 1

Related Questions