• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2657
  • Last Modified:

How to search for phone numbers in mysql?

I have a table which is full of arbitrarily formatted phone numbers. These could be
(069) 123456
069-123456
06101 36 36 11
These are just a few examples.

Now I want to find all rows in my MySQL table that match a phone number in a well defined format that has only digits (no spaces, no extra characters). E.g.:
026362532343

I don't have the option of adding extra columns in the table.

How would you create an SQL statement that finds the matching rows?
0
dirkil2
Asked:
dirkil2
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Gurvinder Pal SinghCommented:
Just to give an idea
SELECT * FROM events WHERE phoneNumber REGEXP '[0-9]*'

http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/
0
 
dirkil2Author Commented:
That doesn't make sense at all because this returns all rows that for which the phone column has only digits. That's not what I am looking for. Maybe I wasn't clear enough. I re-phrase my question by giving an example:

Input: 01239977565
Output: all records for which column phone matches the above input.

Column phone could have extra characters like -, (, ), / and the like.

For example a match would be a record with: 0123-99 77 565
A record with (012) 77 3834738 would not match.
0
 
Gurvinder Pal SinghCommented:
<<Now I want to find all rows in my MySQL table that match a phone number in a well defined format that has only digits (no spaces, no extra characters>>

<<That doesn't make sense at all because this returns all rows that for which the phone column has only digits.>>
Aren't these 2 statements contradictory?

any ways, use this regular expression
([0-9](\s|\-){0-1})*
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Philip PinnellCommented:
A bit clunky but you could

SELECT * FROM events WHERE replace(replace(replace(replace(replace(phoneNumber,' ',''),'(','') ,')',''),'-',''),'/','') = @phonenumber




~
1
 
dirkil2Author Commented:
Please read my question carefully. I specify an input phone number and the task is to match it. In your answer the input phone number doesn't even appear.
0
 
dirkil2Author Commented:
My last comment was for gurvinder372.

@andycrrofts: Yes I had that myself but I was hoping to find a better (faster) solution.
0
 
Gurvinder Pal SinghCommented:
@dirkil2: Thanks, i have spotted that one now. My apologies for posting irrelevant solution.
0
 
Philip PinnellCommented:
How about tidying up the table and removing all these extraneous characters. Then searching will be simple and quick.

Ensuring newly added ones were stripped of non numerics would be necessary but this would be a better solution all round
0
 
cminearCommented:
This may be a bad assumption, but instead of using the only digits phone number ('026362532343'), if you used the just digits phone number with "[^0-9]*" between every digit, you could do a simple query using REGEXP.  While I cannot figure out how to do this (easily) with MySQL functions, most scripting languages should be able to do that.

Using your sample data and the initial all-digits phone number of '069123456', I tried the following.  (See below.)  This seems to be exactly what you want.

Still, I would agree with andycrofts' point about tidying up the database and then modifying the entry points to prevent further "dirtying".
mysql> select * from test;
+----------------+
| phoneNumber    |
+----------------+
| (069) 123456   |
| 069-123456     |
| 06101 36 36 11 |
+----------------+
3 rows in set (0.00 sec)

mysql> select phoneNumber from test where phoneNumber REGEXP '[^0-9]*0[^0-9]*6[^0-9]*9[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*';
+--------------+
| phoneNumber  |
+--------------+
| (069) 123456 |
| 069-123456   |
+--------------+
2 rows in set (0.00 sec)

mysql>

Open in new window

0
 
dirkil2Author Commented:
After due consideration I managed to convincesome people to change the database structure and add a field that stores the plain digits of the telephine number.

I thank all of you who participated in finding a solution.
0
 
Philip PinnellCommented:
thanks
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now