CODEWITHSUNDEEP
sqlregexoracle-databaseselect
Oliver
Oliver

Reputation: 23510

Oracle SQL - Matching a phone format

I want to find datas that do not match the following pattern :

To do that, I use this query, but it does not seem to work :

select distinct regexp_replace(phonenumber, '[0-9]', '') 
from coord 
where REGEXP_LIKE(phonenumber, '^[ ./]{*}[0-9]{2}[ ./]{*}[0-9]{2}[ ./]{*}[0-9]{2}[ ./]{*}[0-9]{2}[ ./]{*}[0-9]{2}[ ./]{*}$')

What did I do wrong ?

Upvotes: 0

Views: 3761

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Assuming you want to search for the records that DO match your pattern, just replace your {*} with * :

SELECT DISTINCT regexp_replace(phonenumber, '[0-9]', '')
  FROM coord 
 WHERE regexp_like(phonenumber,
                   '^[ ./]*[0-9]{2}[ ./]*[0-9]{2}[ ./]*[0-9]{2}[ ./]*'
                   ||'[0-9]{2}[ ./]*[0-9]{2}[ ./]*$')

If you want to select the records that DO NOT match this pattern:

SELECT DISTINCT regexp_replace(phonenumber, '[0-9]', '')
  FROM coord 
 WHERE NOT regexp_like(phonenumber,
                       '^[ ./]*[0-9]{2}[ ./]*[0-9]{2}[ ./]*[0-9]{2}[ ./]*'
                       ||'[0-9]{2}[ ./]*[0-9]{2}[ ./]*$')

Upvotes: 3

Related Questions