CODEWITHSUNDEEP
sqloracle-database
Cheryl Hubert
Cheryl Hubert

Reputation: 39

Find phone numbers with unexpected characters using SQL in Oracle?

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:

Tried this:

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

Still getting rows where phone has numbers.

Upvotes: 3

Views: 3165

Answers (4)

sticky bit
sticky bit

Reputation: 37482

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.

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

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.

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

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

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

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]*$')

Upvotes: 3

Related Questions