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.

I would add a column without the spaces, just for the search feature. — Cristian Lupascu
– Cristian Lupascu, Commented Sep 28, 2013 at 13:59
What @w0lf said. And probably look into FULLTEXT to handle such searches — planestepper
– planestepper, Commented Sep 28, 2013 at 14:16

Chamal · Accepted Answer · 2013-09-28 14:04:18Z

3

You could use replace function

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

answered Sep 28, 2013 at 14:04

Chamal

1,44910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related