mySQL regex in the where clause

Question

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

What do you mean by "any number format"? Do you mean any string that contains at least one digit? — Mark Byers
– Mark Byers, Commented Aug 10, 2010 at 19:18
If speed is at all important, you should clean the data in the database and avoid using a regex. :-) — John Parker
– John Parker, Commented Aug 10, 2010 at 19:34

OMG Ponies · Accepted Answer · 2010-08-10 19:30:23Z

64

Use:

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

Reference:

Pattern Matching

answered Aug 10, 2010 at 19:30

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Elhotiby Over a year ago

what if i have a value 6309991234 but in the database its in (630)9991234 can i add a REGEXP '^1[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$'="6309991234"

OMG Ponies Over a year ago

@John: The brackets support "m,n", so you could use {3,7} to capture "9991234". I'm still messing with the functionality to get optional character support working...

sth · Accepted Answer · 2013-07-28 20:06:13Z

1

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

edited Jul 28, 2013 at 20:06

sth

231k56 gold badges288 silver badges370 bronze badges

answered Jul 28, 2013 at 18:22

jacob YO

111 bronze badge

Comments

dockeryZ · Accepted Answer · 2010-08-10 19:20:45Z

0

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.

answered Aug 10, 2010 at 19:20

dockeryZ

4,0011 gold badge22 silver badges30 bronze badges

3 Comments

Matt Elhotiby Over a year ago

I am not doing the insert so i just need to pull out

Matthew Over a year ago

This can also be problematic when you need to store a leading 0 for the exchange. Not that he leads to that requirement above.

Emile P. Over a year ago

Use VARCHAR but sanitize it before insertion (e.g. get rid of spaces/dashes/...)

Collectives™ on Stack Overflow

mySQL regex in the where clause

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related