If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`?

Question

My online-shop in PHP (OpenCart). On site we have product-search. For example product have name iSport 220, and if user search input string is iSport220 we must show this product. And vice versa.

First my thought was about LIKE operator, but I see that I need something different. In list of MySQL string functions I do not find any useful. What the best practice for this kind of problem? Version of MySQL is 5.5.

Answer 1

Replace space and compare: ... where replace('xxx yyy',' ','') = 'xxxyyy'

or generally it can be: ... where replace(column,' ','') = 'xxxyyy'

But not that this may not like your indexes on the columns.

Answer 2

I found the better solution: on php-level surround user's input by % and remove spaces:

Regex search for numbers:

preg_match('/\d+/', $search, $matches);

Replace substring with numbers to substring with numbers surrounded by %:

$search = str_replace($matches[0], '%'. $matches[0] . '%', $search);

Remove spaces:

$search = str_replace(' ', '', $search);

Answer 3

You can REPLACE the spaces in xxx yyy and xxxyyy, so when you make a search it will look for xxxyyy:

SELECT 
    *
FROM test.tbl1
WHERE REPLACE(tbl1.searchfield,' ','') = REPLACE('xxxyyy');