Solved

Help with a regular expression for UK Phone Numbers

Posted on 2007-03-20
1
339 Views
Last Modified: 2008-03-06
I am having trouble getting a regular expression to work in mySQL. The expression came from http://regexlib.com/REDetails.aspx?regexp_id=593 but when I try and test it I get the following error:-

select count(*) from test.PHONE_NUMBERS where phone not regexp '^(((\+44\s?\d{4}|\(?0\d{4}\)?)\s?\d{3}\s?\d{3})|((\+44\s?\d{3}|\(?0\d{3}\)?)\s?\d{3}\s?\d{4})|((\+44\s?\d{2}|\(?0\d{2}\)?)\s?\d{4}\s?\d{4}))(\s?\#(\d{4}|\d{3}))?$';
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

Any ideas?

0
Comment
Question by:chocobogo
1 Comment
 
LVL 22

Accepted Solution

by:
NovaDenizen earned 200 total points
ID: 18757362
That looks like a perl regexp, and mysql regexps are a little different.  See http://dev.mysql.com/doc/refman/5.0/en/regexp.html

\+ is intended to be the literal '+' character.  Mysql needs to see \\+ to get this right, because it does backslash escapes before it starts parsing the regexp.

\+ -> \\+

Same with \# and \( and \)

\# -> \\#
\( -> \\(
\) -> \\)

\d is intended to be a digit, and mysql doesn't support this method.  Use [0-9] instead.

\d -> [0-9]

\s is whitespace, not supported by mysql.  Use [[:space:]]

\s -> [[:space:]]

I think that covers all the dialect differences.  In the end the only backslashes present should be in the double-escaped literal (like \\#) patterns.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now