Solved

Help with a regular expression for UK Phone Numbers

Posted on 2007-03-20
1
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 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