Search code examples
mysqlsqlregexstringinnodb

Matching strings without space and punctuation in MySQL

I'm working on a query which I thought should be quite intuitive, but somehow I'm facing a bit of issues when implementing it. I guess what I'm trying to achieve is to match a string stored in MySQL DB without space and punctuation (other creative approaches are more than welcome). At the same time I would like the query to handle Unicode characters in diacritics insensitive fashion (so options like REGEXP are kinda out of luck). And the last condition is I'm on MySQL 5.5 with InnoDB engine, so full-text indexing is not supported (but I'm open to upgrade to 5.6/5.7 if it helps sorting this out).

Consider the scenario which the string Hello-World from John Doe is stored in DB. I would like to find it when given the search string HelloWorld or JohnDoe. To be more general, the string in DB can contain brackets, understores and any other punctuation (not limited to ASCII but can compromise for now), while the search string can be a combination of words with or without any separators in between. The closest I've gotten so far is to daisy chain the REPLACE function for a list of known punctuation, like below:

SELECT text FROM table WHERE REPLACE(REPLACE(text, '-', ''), ' ', '') LIKE '%JohnDoe%'

My questions are:

  1. Is there a better way instead of using the daisy chain above?
  2. If that's the only solution, how will the performance be impacted when I chain up hundred or more REPLACE functions?

Thanks in advance for your help.

Solution

  • I don't know how restrictive your searches must be, but you could try to strip out all non-alphanumeric characters from it, so that you end up with a string like "HelloWorldfromJohnDoe" that you match with instead.

    Have a look at this answer: How to remove all non-alpha numeric characters from a string?

    You might have to change it around a bit though to make it fir your purposes. I changed it from CHAR(32) to CHAR(255) to make sure I could get the column, but you might want to look into changing the function altogether to fit your data more precisely.

    Then you something like this:

    SELECT *
FROM testing
WHERE alphanum(test) LIKE CONCAT('%', alphanum('John Doe'), '%')

    which should give you a hit.