Henry Henry - 2 years ago 128
SQL Question

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

I have a table with a

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 Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download