0

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.

1
  • Can you please clarify exactly what you are asking? Where does 'xxx yyy' come from? What is it? It might be useful to state what version of MySQL are you using. Commented Jul 21, 2015 at 14:32

3 Answers 3

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.

2
  • What about if in db I have xxx yyy and user input is xxxyyy without spaces? Commented Jul 21, 2015 at 14:49
  • That's why we are replacing space in column.
    – mysql_user
    Commented Jul 21, 2015 at 15:53
0

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);

0

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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.