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.

Comments are closed.

Post Navigation