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'