SQL create OR condition from comma delimated string

Question

I'm having the following table in my database.

I'm firing the below query to get some keywords:

SELECT CONCAT('%',replace (keyword, ' ','%'),'%') FROM `ads` WHERE "rent 
car"  LIKE CONCAT('%',replace (keyword, ' ','%'),'%')

This is working fine and giving me two results %car% and %rent%car%

But the query will not be fired for last record as there are two many keywords by comma separated so that I need the same query with multiple or conditions for each comma delimited keyword like below (if there are comma separated strings in a row otherwise it should be same as above):

SELECT CONCAT('%',replace (keyword, ' ','%'),'%') FROM `ads` WHERE ("rent 
home"  LIKE '%rent%home%') OR ("rent 
home"  LIKE '%rental%homes%') OR ("rent 
home"  LIKE '%rent%2bhk%') OR ("rent 
home"  LIKE '%rent%3bhk%')

I know, I will need a SQL function but could not get such kind of function. How can I do that?

Answer 1

As per my understanding, you want to search keyword (multi-word) by replacing space with %, so that it should match %pre-word%post-word%.

e.g. for keyword rent home, should search rent home, rental home, rental homes etc

If right, following query should satisfy your requirement:

SELECT keyword FROM `ads` WHERE keyword 
LIKE CONCAT('%', replace('rent home', ' ', '%'), '%');

DEMO