Solved

Help with a regular expression for UK Phone Numbers

Posted on 2007-03-20
1
346 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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