如何忽略 MYSQL SELECT LIKE %...% 上的字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9850180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to ignore characters on a MYSQL SELECT LIKE %...%
提问by Henry
I have a table with a phonecolumn, where data may have spaces, dots,dashes or + signs between the numbers.
I need to do a search with LIKE wildcards that ignore all those characters, for example:
我有一个带有一phone列的表格，其中数据在数字之间可能有空格、点、破折号或 + 号。我需要使用忽略所有这些字符的 LIKE 通配符进行搜索，例如：
- a record may have phone as "+123-456 78.90"
- a query looking for "6789" or any complete or incomplete sequence of proper digits in order should bring up that record.
- 记录可能有电话“+123-456 78.90”
- 按顺序查找“6789”或任何完整或不完整的正确数字序列的查询应显示该记录。
Unfortunately I cant cleanup the original table to remove the non-digit characters and do a plain SELECT LIKE %...%.
不幸的是，我无法清理原始表以删除非数字字符并执行简单的 SELECT LIKE %...%。
MYSQL has functions to substitute/remove characters from strings, but can't find a way to use them inside a query with a widlcarded LIKE.
MYSQL 具有替换/删除字符串中字符的功能，但找不到在带有通配符 LIKE 的查询中使用它们的方法。
Any help will be much appreciated.
任何帮助都感激不尽。
回答by Hari
I see two ways doing this:
我看到有两种方法可以做到这一点：
If you allow only a few extra characters than you can prepare a string which is stripped from these extra characters and you use the LIKE operator you normally would
select * from phoneTable where replace(replace(phone, '+', ''), '-', '') LIKE '%123%'Of course you need as many replace calls as the number of allowed extra characters
You use regular expressions, let's say you are searching for pattern 123
select * from phoneTable where phone REGEXP '.*1[^0-9]*2[^0-9]*3'
如果您只允许一些额外的字符，那么您可以准备一个从这些额外字符中剥离的字符串，并且您通常会使用 LIKE 运算符
select * from phoneTable where replace(replace(phone, '+', ''), '-', '') LIKE '%123%'当然，您需要与允许的额外字符数一样多的替换调用
您使用正则表达式，假设您正在搜索模式 123
select * from phoneTable where phone REGEXP '.*1[^0-9]*2[^0-9]*3'
回答by Frank
I had the same problem with partnumbers and unwanted characters. My solution was
我在零件编号和不需要的字符方面遇到了同样的问题。我的解决方案是
SELECT * FROM parts WHERE replace(partnumber, '-', '') LIKE '$searchterm%';
