Oracle SQL - Find special non numeric characters in field

Question

How can I identify the datas that have anything else than a number in them in a where clause ?

Answer 1

SELECT *
  FROM <table>
 WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');

Hope it helps...

Answer 2

You can also use the TRANSLATE function to do this, as follows:

SELECT *
  FROM A_TABLE a
  WHERE LENGTH(TRANSLATE(a.FIELD, 'x0123456789', 'x')) IS NOT NULL

The expression LENGTH(TRANSLATE(a.FIELD, 'x0123456789', 'x')) will return NULL if the field contains only numeric characters. If non-numeric characters are present it will return the number of non-numeric characters.

Share and enjoy.