代码之家 › 专栏 › 技术社区 › Tauren

SQL返回在MySQL中删除非数字字符的字段

database mysql sql

Tauren · 技术社区 · 15 年前

我有一个mysql表,其中包含可以用多种不同方式格式化的电话号码。例如:

(999)99—99
99—99—99
99 99 99 99 99
999 999 999
+1 999 999 999
九十九亿九千九百九十九万九千九百九十九
019999999999
等。

字段存储为字符串。我只想返回删除了任何非数字的字段。在MySQL中,可以使用什么SQL来实现这一点?因此,上面的值都将返回99999999、19999999999或019999999999。

注意,这是一个不定期运行的查询,所以我不需要担心优化、索引或其他任何问题。但是,我确实需要将它包含到与其他几个表的相当复杂的连接中。在最基本的层面上,我试图找到重复的记录,并希望按电话号码分组,或者至少按电话号码排序。

5 回复 | 直到 9 年前

Neil Knight 15 年前

你可以用 REPLACE 命令用不同的变体封装整个字符串,以便删除它们。

Your Common Sense 15 年前

在将这些数字插入数据库之前,必须对它们进行规范化。
使其仅存储在数字中。
否则,要想把它弄好会让人头疼。

Community CDub 8 年前

有一个UDF可用于regex替换(参见 How to do a regular expression replace in MySQL? )

或者您可以使用子字符串和ASCII代码检查来编写自己的UDF。

vava 15 年前

这是针对小型regexp的工作,但不幸的是,mysql不支持regexp replace(除非您希望 recompile it from sources )所以我想说在代码方面进行清理,会容易得多。

VenerableAgents 14 年前

我也有同样的问题!你可以看到我 solved it here .

以下是我必须创建和使用的功能,这些功能非常有用:

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = REPLACE(val,".","");
 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
END;

CREATE FUNCTION FormatPhone (val VARCHAR(255))
 RETURNS VARCHAR(255)
BEGIN
  SET val = RIGHT(CONCAT("0000000000",NumericOnly(val)),10);
  RETURN CONCAT("(",LEFT(val,3),") ",LEFT(RIGHT(val,7),3),"-",RIGHT(val,4));
END;

这样做可以格式化您的电话号码:

SELECT FormatPhone(PhoneNumberColumn) FROM TableName;

或者这样只获取数字:

SELECT NumericOnly(PhoneNumberColumn) FROM TableName;