mysql 组字段名称消除所有非字母字符

Question

我有一个表格，其中输入的电话号码如下所示

tel
--------
+6112345
611-12345
61112345
611 12345

所有这些都是通过不同方式输入的同一个电话号码。我如何编写一个查询将所有这些组合为一个？

我写了一个查询，例如 -

select replace(replace(replace(tel,"+",""),"-","")," ",""), count(*)  
from tb_orders 
group by replace(replace(replace(tel,"+",""),"-","")," ","")

请帮我解决这个问题!

Answer 1

源数据

SELECT * FROM tb_orders;
+-----------+
| tel       |
+-----------+
| +6112345  |
| 611-12345 |
| 61112345  |
| 611 12345 |
+-----------+
4 rows in set (0.00 sec)

查询:

SELECT REPLACE(REPLACE(REPLACE(tel,"+",""),"-","")," ","") AS PhoneNumber , COUNT(*) FROM tb_orders GROUP BY PhoneNumber;
+-------------+----------+
| PhoneNumber | COUNT(*) |
+-------------+----------+
| 61112345    |        3 |
| 6112345     |        1 |
+-------------+----------+
2 rows in set (0.00 sec)