CODEWITHSUNDEEP
mysqlregex
JDoc
JDoc

Reputation: 41

Filtering phone numbers REGEXP

I have a MySQL query to find 10 digit phone numbers that start with +1

SELECT blah
FROM table
WHERE phone REGEXP'^\\+[1]{1}[0-9]{10}$'

How can I filter this REGEXP further to only search certain 3 digit area codes? (ie. International 10 digit phone numbers who share US number format)

I tried using the IN clause ie. IN('+1809%','+1416%') but ended up with error in syntax

WHERE phone REGEXP'^\\+[1]{1}[0-9]{10}$' IN('+1809%','+1416%')

Upvotes: 1

Views: 418

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626851

You may use a grouping construct with an alternation operator here, like

REGEXP '^\\+1(809|416)[0-9]{7}$' 
             ^^^^^^^^^

Just subtract 3 from 10 to match the trailing digits. Note that in MySQL versions prior to 8.x, you cannot use non-capturing groups, you may only use capturing ones.

Also, [1]{1} pattern is equal to 1 because each pattern is matched exactly once by default (i.e. {1} is always redundant) and it makes littel sense to use a character class [...] with just one single symbol inside, it is meant for two or more, or to avoid escaping some symbols, but 1 does not have to be escaped as it is a word char, so the square brackets are totally redundant here.

Upvotes: 1

Related Questions