Blog Oracle

find records with non-numeric characters in oracle

There are times when you need to find dirty data on your database. This is especially true when your data capture has no validation. In that note, we have a sample here where we need to find the records where there are non-numeric numbers in a field in a particular table.


SELECT accnt.contact_no data_field,
CASE WHEN regexp_like(accnt.contact_no, '^-?[[:digit:].]*$')
THEN 'Numeric'
ELSE 'Non-Numeric'
END AS type
FROM accounts accnt
WHERE accnt.accnt_type_cd = 'Customer'

oracle_plsql_regexp_numeric

So if you want to just show those with non-numeric characters, you can set the query as below.

SELECT accnt.contact_no data_field,
CASE WHEN regexp_like(accnt.contact_no, '^-?[[:digit:].]*$')
THEN 'Numeric'
ELSE 'Non-Numeric'
END AS type
FROM accounts accnt
WHERE accnt.accnt_type_cd = 'Customer'
AND NOT regexp_like(accnt.contact_no, '^-?[[:digit:].]*$')

A Bit of Explanation


This technique uses regular expression (regexp_like) to fetch the desired data. The pattern "'^-?[[:digit:].]*$'" basically says that extract only those records that starts and ends with numbers or dot. If you want to include commas, then the pattern should look like "'^-?[[:digit:].,]*$'".

Though this query can be done in other ways, I believe regexp_like is the most suitable and more efficient way.

I hope this helps.