Issue with RegExp matching a phone number format in query

Question

I am trying to write a RegExp for MySQL that will only return the following 3 phone formats:

+1 000-000-0000 x0000

+1 000-000-0000

000-000-0000 x0000

000-000-0000

So basically:

+[any number of digits][space][any three digits]-[any three digits]-[any four digits][space][x][any number of digits]

The country code and the extension are optional. I am new to these but I would think this should return at least a number including both country code and extension options, but I get 0 results when I execute it.

\x2B[0-9]*\x20[0-9]{3}\-[0-9]{3}\-[0-9]{4}\x20x[0-9]+| \x2B[0-9]*\x20[0-9]{3}\-[0-9]{3}\-[0-9]{4}| [0-9]{3}\-[0-9]{3}\-[0-9]{4}

Can someone tell me why I am getting 0 results even though I have records like +1 555-555-5555 x5555 in my db. Also what is the syntax to make the country code and the extension are optional

Please note I am using [0-9] because I am querying a text field and \d didn't seem to return anything even when my criteria was something simple like \d*

Why are you using \x2B instead of just escaping the +: \+? Is there a particular reason? And why are you escaping the spaces (i.e. \x20 instead of ` `) — anon
– anon, Commented May 1, 2015 at 14:30
Basically because I did not know you could do it that way :) — user3167249
– user3167249, Commented May 1, 2015 at 17:05

dmgig · Accepted Answer · 2015-05-01 17:39:21Z

So, as a joint effort, turns out the answer is here:

SELECT * FROM table WHERE field REGEXP '(^[+][0-9]+\ )?([0-9]{3}\-[0-9]{3}\-[0-9]{4})(\ x[0-9]+$)?'

First was the fact that character codes in mysql (x20, x2B and the like) are not allowed. Next important step was the use of parenthesis and the "?" token to make the different sections optional.

============

I think the issue is the lack of parenthesis to define the subexpressions.

This seems to work out for me, though it doesn't look real pretty:

SELECT '+1 000-000-0000 x0000' REGEXP '^(([+][0-9]*\[ ][0-9]{3}\-[0-9]{3}\-[0-9]{4}[ ]x[0-9]+)|())$'

as does

SELECT '' REGEXP '^(([+][0-9]*\[ ][0-9]{3}\-[0-9]{3}\-[0-9]{4}[ ]x[0-9]+)|())$'

as does (plain phone number):

SELECT '555-555-5555' REGEXP '^(([+][0-9]*\[ ])*[0-9]{3}\-[0-9]{3}\-[0-9]{4}([ ]x[0-9]+)*)|())$'

EDIT: (same regexp as above, but testing against version w/ country code and extension):

SELECT '+1 555-555-5555 x55' REGEXP '^(([+][0-9]*\[ ])*[0-9]{3}\-[0-9]{3}\-[0-9]{4}([ ]x[0-9]+)*)|())$'

When I try yours:

SELECT '+1 000-000-0000 x0000' REGEXP '\x2B[0-9]*\x20[0-9]{3}\-[0-9]{3}\-[0-9]{4}\x20x[0-9]+|'

I get:

1139 - Got error 'empty (sub)expression' from regexp

I found out you can not use chr codes in mysql. I needed to escape. This is what I finally ended up with that seems to work: ^\\+[0-9]+\ |^[0-9]{3}\-[0-9]{3}\-[0-9]{4}$|\ x[0-9]+$
If that's the problem you had, please post an answer detailing it and accept it.
I still have not figured out the complete answer. I need to know how to make the country code and extension optional. What I have right now is basically any of the three.
@dgig it turns out I was looking for (expression)? For anyone reading grouping with () and adding a trailing? makes it optional. I was able to finally solve it with the following expression: SELECT * FROM table WHERE field REGEXP '(^[+][0-9]+\ )?([0-9]{3}\-[0-9]{3}\-[0-9]{4})(\ x[0-9]+$)?' I would not have been able to get there without walking through your examples so if you would like to update your answer I will select it as the answer.

Collectives™ on Stack Overflow

Issue with RegExp matching a phone number format in query

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related