CODEWITHSUNDEEP
mysqlregex
Matt Elhotiby
Matt Elhotiby

Reputation: 44066

mySQL regex in the where clause

SELECT telephone_number
FROM table
WHERE telephone_number REGEXP '^1[() -]*999[() -]*999[() -]*9999$';

how do i make so its valid for any number format and any number like 

407-888-0909
1(408)998-7654
7776654433
876-7788

right now its only valid for 1-999-999-9999

Upvotes: 43

Views: 74635

Answers (3)

jacob YO
jacob YO

Reputation: 11

SELECT telephone_number
  FROM table
 WHERE telephone_number REGEXP '[1]?[(]?[[:DIGIT:]]{3}[)]?[-]?[[:DIGIT:]]{3}[-]?[[:DIGIT:]]{4}'

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

SELECT telephone_number
  FROM table
 WHERE telephone_number REGEXP '^1[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$';

Reference:

Upvotes: 64

dockeryZ
dockeryZ

Reputation: 3981

It isn't very wise to store phone numbers in a database with spaces, dashes, parentheses, etc. The most efficient way is to truncate all that garbage to a simple 10 digit number. That way you can actually store the number in an INTEGER based column instead of a VARCHAR.

Upvotes: 0

Related Questions