从电话号码中删除特殊字符并进行比较

This is my table structure:

这是我的表格结构：

|id  |Phone_number         
|98  |+91 9033601870
|208 |+44 6547891235
|227 |+93 35465465
|229 |+1 9033601870
|259 |+1 9033601870
|374 |+ 1 97 2- 71 4- 01 17
|375 |+1 972-714-0117
|376 |+92 97271 40117
|444 |+1 888-888-8888
|445 |+1 441-562-3124
|446 |9727140117

Now i want all the result which has number 9727140117

现在我想要所有结果编号9727140117

I have tried below things.

我试过下面的事情。

SELECT acp.*
FROM `acd_contactlist_phone` acp
WHERE acp.Phone_number like '%9727140117%';

This query give me wrong results.This query give me only last record.

这个查询给我错误的结果。这个查询只给我最后一条记录。

My Expected result is:

我的预期结果是：

 |id  |Phone_number   
 |374 |+ 1 97 2- 71 4- 01 17
 |375 |+1 972-714-0117
 |376 |+92 97271 40117
 |446 |9727140117

Current Query result is :

当前查询结果是：

 |id  |Phone_number   
 |446 |9727140117

4 个解决方案

#1

You can use replace():

你可以使用replace（）：

SELECT acp.*
FROM `acd_contactlist_phone` acp
WHERE replace(replace(replace(acp.number, ' ', ''), '-', ''), '+', '') like '%9727140117%';

Unfortunately, MySQL doesn't have regular expression replace or a translate() function.

不幸的是，MySQL没有正则表达式替换或translate（）函数。

Another option would be a complex regular expression:

另一种选择是复杂的正则表达式：

where acp.number regexp '9[- +]*7[- +]*2[- +]*7[- +]*1[- +]*4[- +]*0[- +]*1[- +]*1[- +]*7[- +].*'

#2

You can create one simple function which removes all special characters and alphabet from your String as shown below.

您可以创建一个简单的函数，从String中删除所有特殊字符和字母，如下所示。

DROP FUNCTION IF EXISTS onlynum; 
    DELIMITER | 
    CREATE FUNCTION onlynum( str CHAR(32) ) RETURNS CHAR(16) 
    BEGIN 
      DECLARE i, len SMALLINT DEFAULT 1; 
      DECLARE ret CHAR(32) DEFAULT ''; 
      DECLARE c CHAR(1); 
      SET len = CHAR_LENGTH( str ); 
      REPEAT 
        BEGIN 
          SET c = MID( str, i, 1 ); 
          IF c REGEXP '[[:digit:]]' THEN 
            SET ret=CONCAT(ret,c); 
          END IF; 
          SET i = i + 1; 
        END; 
      UNTIL i > len END REPEAT; 
      RETURN ret; 
    END | 
    DELIMITER ; 
    ;

And then execute below query.

然后执行以下查询。

SELECT acp.*
FROM `acd_contactlist_phone` acp
WHERE onlynum(acp.number) like '%9727140117%';

Try above code.

试试上面的代码。

#3

The query is returning correct, it wouldn't consider space/- so it will return all required result, if you reduce the wildcard check to less number of digits.

查询返回正确，它不会考虑空格/ - 因此如果您将通配符检查减少到更少的位数，它将返回所有必需的结果。

SELECT acp.*
FROM `acd_contactlist_phone` acp
WHERE acp.Phone_number like '%0117%';

#4

With REGEXP operator:

使用REGEXP运算符：

SELECT acp.*
FROM `acd_contactlist_phone` acp
WHERE acp.Phone_number REGEXP '9[^0-9]*7[^0-9]*2[^0-9]*7[^0-9]*1[^0-9]*4[^0-9]*0[^0-9]*1[^0-9]*1[^0-9]*7';

#1