Mysql Question posted in
The official MySQL documentation can be found here.

Check if any word of a string exists in a column with comma-separated values in mysql

I have this string "I need red budget car".

I have a table where in column keywords I have comma-separated words. I know that I can use find_in_set to search if a specific word (ex.budget or car) exists in that column like this for example:

select * from products where find_in_set('budget',keywords) <> 0

However, I can’t find a way to check whether any of the words from the string exist if the keywords column.

Of course, I can separate the string by words and run the above query for each word, but that would increase the execution time, so I am looking for some way to do it in one query.

Update

Maybe I can dynamically create the query and create OR statement for each word of the string, like:

SELECT * FROM products WHERE FIND_IN_SET('I', keywords) OR FIND_IN_SET('need', keywords) OR FIND_IN_SET('a', keywords) OR FIND_IN_SET('budget', keywords) OR FIND_IN_SET('car', keywords)

But there must be a more elegant solution I guess?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have found the best answer for my case. It's a function that you compare one comma-separated list to another, directly inside the select query.

    Here is the function you have to add to MySQL

    DELIMITER $$

CREATE FUNCTION `FIND_IN_SET_X`(inputList TEXT,targetList TEXT) RETURNS INT(11)
    DETERMINISTIC
BEGIN
  DECLARE limitCount INT DEFAULT 0 ;
  DECLARE counter INT DEFAULT 0 ;
  DECLARE res INT DEFAULT 0 ;
  DECLARE temp TEXT ;
  SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',', '')) ;
  simple_loop :
  LOOP
    SET counter = counter + 1 ;
    SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList, ',', counter),',',- 1) ;
    SET res = FIND_IN_SET(temp, targetList) ;
    IF res > 0 
    THEN LEAVE simple_loop ;
    END IF ;
    IF counter = limitCount 
    THEN LEAVE simple_loop ;
    END IF ;
  END LOOP simple_loop ;
  RETURN res ;
END$$

DELIMITER ;

    Then instead of using FIND_IN_SET(), you can use the FIND_IN_SET_X() function that you created.

    So the query would be

    select * from products where FIND_IN_SET_X('I,need,a,budget,car',keywords) <> 0

    I took this function from this post mysql check if numbers are in a comma separated list


  2. 0

    Before using FIND_IN_SET() in MySQL you need to know about the following

    • column contains comma-separated values without any extra spaces
    • If extra space is there then you should replace with some other characters
    SELECT * FROM Products WHERE FIND_IN_SET('budget', REPLACE(keywords, ' ', ',')) > 0;

    before using find_in_Set need to replace space between values with comma-separator works fine.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search