CODEWITHSUNDEEP
mysqlsqlregex
user3167249
user3167249

Reputation: 1102

SELECT all records with text in a phone field

I am trying to write a MySQL query that will select all records where the telephone field has non numeric characters other than a dash or (space)x. All other spaces included.

I want to select all the phone numbers that are formatted: 000 000 0000, (000) 000-0000, 1-800-OurPhoneNumber, 000.000.0000, 000000000 ext 0

Basically I want anything that is not formatted: 000-000-0000 or 000-000-0000 x0000

I know there has to be a REGEXP that can select this, but I can't for the life of me seem to wrap my head around REGEXP's

So far I have SELECT * FROM customer WHERE telephone NOT REGEXP '[0-9]+' but that won't even return customers with outlandish numbers like 1-800-HIDEYOKIDSHIDEYOWIFE

Upvotes: 1

Views: 127

Answers (2)

Angelo Gonzalez
Angelo Gonzalez

Reputation: 61

Yeah, use ^ and $

I always find it useful to get as specific as possible with a regex.

SELECT * FROM customer WHERE telephone NOT REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}|[0-9]{3}-[0-9]{3}-[0-9]{4}\sx[0-9]+$';

Whether you go for a loose or tight regex depends on what data you are trying to get and why.

Upvotes: 0

Barmar
Barmar

Reputation: 781310

You need to anchor the regexp to the beginning and end with ^ and $.

WHERE telephone NOT REGEXP '^[-0-9]+$'

Or you can use a complemented character set:

WHERE telephone REGEXP '[^-0-9]'

Putting ^ at the beginning of a character set makes it match any character not in the set.

Allowing for the optional extension makes it a little more complicated:

WHERE telephone NOT REGEXP '^[-0-9]+( x[0-9]+)?$'

Upvotes: 1

Related Questions