Search string mysql db that can have spaces, be in another string, etc

Question

I have this database wich contains product codes like

EXA 075 11112
0423654
3 574 662 123
JOLA 22354 5
LUCS 2245 785

I use a query with %LIKE% to list the products mathing a string entered by the user for example "22" would list

JOLA 22354 5
LUCS 2245 785

The problem is that the user does not necessarily know the format of the code, so it types in 07511112 and the output is zero, because "EXA 075 11112" is not matched by %LIKE%.

Is there a way to construct the query to trim all spaces from the product field before the search occurs, and then search by the string also trimed of spaces using %LIKE% ? I guess it should then match all entries. Or is there another way ? I cannot run replace ' ', '' on the column, the codes must remains as there are now.

Answer 1

You could use replace function

select *
from mytable
where REPLACE( `productcode` , ' ' , '' ) like '%searchparam%'