Find phone numbers with unexpected characters using SQL in Oracle?

user_10817734 0
Viewed 2523

I need to find rows where the phone number field contains unexpected characters.

Most of the values in this field look like:

123456-7890

This is expected. However, we are also seeing character values in this field such as * and #.

I want to find all rows where these unexpected character values exist.

Expected:

  • Numbers are expected
  • Hyphen with numbers is expected (hyphen alone is not)
  • NULL is expected
  • Empty is expected

Tried this:

WHERE phone_num is not like ' %[0-9,-,' ' ]%

Still getting rows where phone has numbers.

oracle sql
4 Answers

from https://regexr.com/3c53v address you can edit regex to match your needs.

I am going to use example regex for this purpose

select * from Table1
Where NOT REGEXP_LIKE(PhoneNumberColumn, '^[+]*[(]{0,1}[0-9]{1,4}[)]{0,1}[-\s\./0-9]*$')
user_11189780

You can use regexp_like().

...
WHERE regexp_like(phone_num, '[^ 0123456789-]|^-|-$')

[^ 0123456789-] matches any character that is not a space nor a digit nor a hyphen. ^- matches a hyphen at the beginning and -$ on the end of the string. The pipes are "ors" i.e. a|b matches if pattern a matches of if pattern b matches.

user_96614240

You can use translate()

...
WHERE translate(Phone_Number,'a1234567890-', 'a') is NOT NULL

This will strip out all valid characters leaving behind the invalid ones. If all the characters are valid, the result would be NULL. This does not validate the format, for that you'd need to use REGEXP_LIKE or something similar.

user_46873550

Oracle has REGEXP_LIKE for regex compares:

WHERE REGEXP_LIKE(phone_num,'[^0-9''\-]')

If you're unfamiliar with regular expressions, there are plenty of good sites to help you build them. I like this one

user_54200080
Related