Menu
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:
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.
938
add a column without symbols in your table, and search on it. Otherwise use REGEXP but its slow. Cleaning up data before INSERT/UPDATE is half the solution. (Typically done using triggers.)
In some situations, we may not want a space or another special character, so we can include the special character that we want to exclude in our not regular expression, such as [^A-Z0-9 ] to exclude a space.
The LIKE statement is used for searching records with partial strings in MySQL. By default the query with LIKE matches case-insensitive recores. Means query will match both records in lowercase or uppercase.
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'
I had the same problem with partnumbers and unwanted characters. My solution was
SELECT * FROM parts WHERE replace(partnumber, '-', '') LIKE '$searchterm%';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
