mysql like 否定,如何忽略MYSQL SELECT 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:

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.

Unfortunately I cant cleanup the original table to remove the non-digit characters and do a plain 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.

Any help will be much appreciated.

解决方案

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'