mysql - Perfect SQL Query for Searching products

Question

i am looking for the perfect SQL Query to search for products.

Now i am using WHERE LIKE '%...%' but i have problems that i dont found "related" products than.

Example: If i search Dampfbügeleisen (its german) than i only get results that includes exactly this word.

But i want also other products that has a thing of the word in his own name. Like bügeleisen

So, i will have Bügeleisen but also Dampfbügeleisen as result when i search Bügeleisen And also Dampfbügeleisen and Bügeleisen when i search for Dampfbügeleisen

Is something like that possible with just an SQL Query?

question from:https://stackoverflow.com/questions/65672027/perfect-sql-query-for-searching-products

Answer 1

Use the LIKE with CONCAT and make all same case with LOWER:

SELECT product 
FROM products_table
WHERE 'Dampfbügeleisen' LIKE CONCAT('%', LOWER( product), '%') ;

You can check out more details on a similar question here:

https://dba.stackexchange.com/questions/203206/find-if-any-of-the-rows-partially-match-a-string