Searching for phone numbers in mysql

As John Dyer said, you should consider fixing the data in the DB and store only numbers. However, if you are facing the same situation as mine (I cannot run a update query) the workaround I found was combining 2 queries.

The "inside" query will retrieve all the phone numbers and format them removing the non-numeric characters.

SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name

The result of it will be all phone numbers without any special character. After that the "outside" query just need to get the entry you are looking for. The 2 queries will be:

SELECT phone_formatted FROM (
    SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name
) AS result WHERE phone_formatted = 9999999999

Important: the AS result is not used but it should be there to avoid erros.

I know this is ancient history, but I found it while looking for a similar solution.

A simple REGEXP may work:

select * from phone_table where phone1 REGEXP "07[^0-9]*123[^0-9]*456"

This would match the phonenumber column with or without any separating characters.

This looks like a problem from the start. Any kind of searching you do will require a table scan and we all know that's bad.

How about adding a column with a hash of the current phone numbers after stripping out all formatting characters. Then you can at least index the hash values and avoid a full blown table scan.

Or is the amount of data small and not expected to grow much? Then maybe just sucking all the numbers into the client and running a search there.