mysql去除非数字,SQL返回在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.

解决方案

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