Tauren Tauren - 1 year ago 76
MySQL Question

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

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 Source

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