How to search for phone numbers in mysql?

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/

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.

<<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})*

A bit clunky but you could

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




~

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.

My last comment was for gurvinder372.

@andycrrofts: Yes I had that myself but I was hoping to find a better (faster) solution.

@dirkil2: Thanks, i have spotted that one now. My apologies for posting irrelevant solution.

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>

Select allOpen in new window

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.

thanks