SQL返回在MySQL中删除了非数字字符的字段 [英] SQL to return field with non-numeric characters removed in MySQL

问题描述

我有一个包含电话号码的MySQL表格，可以用许多不同的方式格式化。例如：

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

• [phone]


• 999-999-9999


• 999.999.9999


• 999 999 9999


• [phone]


• 9999999999


• 019999999999


• 等。

• [phone]
• 999-999-9999
• 999.999.9999
• 999 999 9999
• [phone]
• 9999999999
• 019999999999
• etc.

字段存储为字符串。所有我想做的是返回任何非数字删除的字段。什么SQL可以用于在MySQL中这样做？所以上面的值都将返回9999999999，19999999999或019999999999。

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.

推荐答案

这篇关于SQL返回在MySQL中删除了非数字字符的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！