如何忽略MYSQL SELECT LIKE％...％上的字符 [英] How to ignore characters on a MYSQL SELECT LIKE %...%

问题描述

我有一个带有phone列的表，其中数据之间的数字之间可能有空格，点，破折号或+号. 我需要使用忽略所有这些字符的LIKE通配符进行搜索，例如:

I have a table with a phone column, 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:

• 一条记录的电话可能是"+ 123-456 78.90"
• 查找"6789"或任何完整或不完整的正确数字序列的查询应调出该记录.

不幸的是，我无法清理原始表以删除非数字字符并执行简单的SELECT LIKE％...％.

Unfortunately I cant cleanup the original table to remove the non-digit characters and do a plain SELECT LIKE %...%.

MYSQL具有替换/删除字符串中的字符的功能，但是找不到在带有类似WIKE的查询中使用它们的方法.

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.

任何帮助将不胜感激.

推荐答案

我看到两种方法:

1. 如果只允许几个多余的字符，则可以准备一个从这些多余的字符中去除的字符串，并使用LIKE运算符，通常情况下

1. 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

您使用正则表达式，假设您正在搜索模式123

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'

这篇关于如何忽略MYSQL SELECT LIKE％...％上的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！