我有一张桌子，上面满是任意格式的电话号码，例如
1
2
3
4
| 027 123 5644
021 393-5593
(07) 123 456
042123456 |
我需要搜索类似任意格式的电话号码(例如07123456应该找到条目(07) 123 456
我将以普通的编程语言执行此操作的方法是将所有非数字字符从"针"中去除，然后遍历大海捞针中的每个数字，将所有非数字字符从中去除，然后与针进行比较，例如(ruby)
1
2
3
4
| digits_only = lambda{ |n| n.gsub /[^\\d]/, '' }
needle = digits_only[input_phone_number]
haystack.map(&digits_only).include?(needle) |
问题是，我需要在MySQL中执行此操作。它具有许多字符串函数，似乎没有一个真正可以满足我的要求。
目前我可以想到2个"解决方案"
-
一起整理CONCAT和SUBSTR的弗兰肯查询
-
在针的每个字符之间插入一个%(就像这样：%0%7%1%2%3%4%5%6%)
但是，这两个都不是特别优雅的解决方案。
希望有人可以提供帮助，或者我可能被迫使用%%%%%%%解决方案
更新：这是对一组相对固定的数据进行操作，可能有几百行。我只是不想做一些可笑的坏事，以致将来的程序员会哭泣。
如果数据集增加，我将采用" phoneStripped"方法。感谢您的所有反馈！
could you use a"replace" function to strip out any instances of"(","-" and"",
我不担心结果是数字。
我需要考虑的主要字符是+，-，(，)和space
那么该解决方案看起来像这样吗？
1
2
3
4
| SELECT * FROM people
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+')
LIKE '123456' |
那不是很慢吗？
这从一开始就是一个问题。您进行的任何类型的搜索都需要进行表扫描，我们都知道这很不好。
在除去所有格式字符后，如何添加带有当前电话号码哈希的列。然后，您至少可以为哈希值建立索引，并避免进行完整的表扫描。
还是数据量很小并且预计增长不会很大？
然后也许只是将所有数字吸入客户端并在其中进行搜索。
我知道这是古老的历史，但是我在寻找类似解决方案时发现了它。
一个简单的REGEXP可能起作用：
1
| select * from phone_table where phone1 REGEXP"07[^0-9]*123[^0-9]*456" |
这将与phonenumber列匹配，带有或不带有任何分隔字符。
这是MySQL的问题-regex函数可以匹配，但不能替换。请参阅此帖子以获取可能的解决方案。
一个开箱即用的想法，但是您是否可以使用"替换"功能删除"("，"-"和""的任何实例，然后使用" isnumeric"功能测试是否结果字符串是数字吗？
然后，您可以对要搜索的电话号码字符串执行相同的操作，并将它们比较为整数。
当然，这不适用于1800-MATT-ROCKS之类的数字。 ：)
我的解决方案将类似于约翰·代尔(John Dyer)所说的。我要添加第二列(例如phoneStripped)，该列会在插入和更新时被剥离。为该列建立索引并对其进行搜索(当然，在删除搜索词之后)。
您也可以添加触发器以自动更新列，尽管我没有使用触发器。但是就像您说的那样，编写MySQL代码来剥离字符串确实很困难，因此仅在客户端代码中执行操作可能会更容易。
(我知道这很晚了，但是我才开始四处看看：)
我建议使用php函数，而不要使用mysql模式，因此您将具有以下代码：
1
2
3
4
5
6
| $tmp_phone = '';
for ($i=0; $i < strlen($phone); $i++)
if (is_numeric($phone[$i]))
$tmp_phone .= '%'.$phone[$i];
$tmp_phone .= '%';
$search_condition .=" and phone LIKE '" . $tmp_phone ."'"; |
请参阅
http://www.mfs-erp.org/community/blog/find-phone-number-in-database-format-independent
因为只有mysql能"看到"它，所以正则表达式在视觉上不会令人吃惊。请注意，您应在正则表达式中使用" *"，而不是""(在OP中以[\\\\ D]开头)。
一些用户担心性能(非索引搜索)，但是在有100000个客户的表中，从用户界面发出此查询后，立即返回，没有明显的延迟。
是否可以运行查询以重新格式化数据以匹配所需格式，然后仅运行简单查询？这样，即使最初的重新格式化很慢，您也没关系。
可能的解决方案可以在以下网址找到：//udf-regexp.php-baustelle.de/trac/
需要安装其他软件包，然后可以使用REGEXP_REPLACE
正如John Dyer所说，您应该考虑将数据固定在数据库中，并且仅存储数字。但是，如果您面临与我相同的情况(我无法运行更新查询)，则发现的解决方法是合并两个查询。
"内部"查询将检索所有电话号码，并将其格式化以除去非数字字符。
1
| SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name |
其结果将是所有没有任何特殊字符的电话号码。之后，"外部"查询仅需要获取您要查找的条目。
这两个查询将是：
1
2
3
| SELECT phone_formatted FROM (
SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') phone_formatted FROM table_name
) AS result WHERE phone_formatted = 9999999999 |
重要提示：不使用AS结果，但应该在该位置以避免错误。
创建用户定义的函数以动态创建正则表达式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| DELIMITER //
CREATE FUNCTION udfn_GetPhoneRegex
(
var_Input VARCHAR(25)
)
RETURNS VARCHAR(200)
BEGIN
DECLARE iterator INT DEFAULT 1;
DECLARE phoneregex VARCHAR(200) DEFAULT '';
DECLARE output VARCHAR(25) DEFAULT '';
WHILE iterator < (LENGTH(var_Input) + 1) DO
IF SUBSTRING(var_Input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
SET output = CONCAT(output, SUBSTRING(var_Input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
SET output = RIGHT(output,10);
SET iterator = 1;
WHILE iterator < (LENGTH(output) + 1) DO
SET phoneregex = CONCAT(phoneregex,'[^0-9]*',SUBSTRING(output, iterator, 1));
SET iterator = iterator + 1;
END WHILE;
SET phoneregex = CONCAT(phoneregex,'$');
RETURN phoneregex;
END//
DELIMITER ; |
在存储过程中调用该用户定义函数。
1
2
3
| DECLARE var_PhoneNumberRegex VARCHAR(200);
SET var_PhoneNumberRegex = udfn_GetPhoneRegex('+ 123 555 7890');
SELECT * FROM Customer WHERE phonenumber REGEXP var_PhoneNumberRegex; |
我是祸。我最终这样做：
1
2
3
| mre = mobile_number && ('%' + mobile_number.gsub(/\\D/, '').scan(/./m).join('%'))
find(:first, :conditions => ['trim(mobile_phone) like ?', mre]) |
这只是一个主意，但是您不能使用Regex快速删除字符，然后与@Matt Hamilton建议的字符进行比较吗？
甚至可以设置一个视图(不确定视图中是否使用mysql)，该视图会将正则表达式剥离的所有电话号码都保留为普通电话号码？
MySQL can search based on regular expressions.
当然可以，但是考虑到任意格式，如果我的干草堆包含"(027) 123 456"(请记住空格的位置可以更改，那么它可能很容易是027 12 3456，而我想与027123456匹配)正则表达式因此需要这个吗？
1
| "^[\\D]+0[\\D]+2[\\D]+7[\\D]+1[\\D]+2[\\D]+3[\\D]+4[\\D]+5[\\D]+6$" |
(实际上情况会更糟，因为mysql手册似乎并不表明它支持\\D)
如果是这种情况，那与我的%%%%%想法差不多吗？
我将使用Google的libPhoneNumber将数字格式化为E164格式。我将添加第二列" e164_number"来存储e164格式的数字并在其上添加索引。
如果这是定期发生的事情，则可能将数据修改为全部一种格式，然后设置搜索表单以去除任何非字母数字(如果允许使用310-BELL这样的数字)，一个好主意。以易于搜索的格式存储数据是成功的一半。
