How to ignore characters on a MYSQL SELECT LIKE %...%

Question

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.

Answer 1

I see two ways doing this:

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

2. 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'
   

Answer 2

I had the same problem with partnumbers and unwanted characters. My solution was

SELECT * FROM parts WHERE replace(partnumber, '-', '') LIKE '$searchterm%';