SQL to return field with non-numeric characters removed in MySQL

Question

I have a MySQL table containing phone numbers that may be formatted in many different ways. For instance:

• (999) 999-9999
• 999-999-9999
• 999.999.9999
• 999 999 9999
• +1 999 999 9999
• 9999999999
• 019999999999
• etc.

The field is stored as a string. All I want to do is return the field with anything non-numeric removed. What SQL could be used to do this in MySQL? So the values above would all return either 9999999999, 19999999999, or 019999999999.

Note this is for a query that will not be run regularly, so I don't need to worry about optimization, indexing, or anything else. However, I do need to include this into a fairly complex join with several other tables. At the most basic level, I'm attempting to find duplicate records and want to group by or at least order by phone number.

Answer 1

You could use the REPLACE command encapsulating the whole string with the different variations in order to remove them.

Answer 2

You have to normalize these numbers before inserting them into database.
Make it stored in numbers only.
Otherwise it would be pain in the neck to get it right.

Answer 3

This is the work for small regexp but unfortunately MySQL does not support regexp replace (unless you want to recompile it from sources). So I'd say do the cleaning on the code side, it'll be much easier.

Answer 4

There's a UDF available for regex replacments (referred to in http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql).

Or you could write your own UDF using a substring and ASCII code check.