Getting the mysql where in delimited string to work

What?
I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room.

The Problem?
When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when converted to a number only retrieves the first value before the first comma. Consider the following, the first query is how MySQL interprets the query and the second is what I want it to do:
copyraw
SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')
  1.  SELECT value FROM my_table WHERE my_id IN ('1, 2, 3') 
  2.  SELECT value FROM my_table WHERE my_id IN ('1', '2', '3') 

The Solution
I must confess that the bulk of this work is not mine and comes from a clever function by Chris Stubben in the MySQL Forums:
copyraw
BEGIN
	DECLARE i INT DEFAULT 0;	-- total number of delimiters
	DECLARE ctr INT DEFAULT 0;	-- counter for the loop
	DECLARE str_len INT;		-- string length,self explanatory
	DECLARE out_str text DEFAULT '';	-- return string holder
	DECLARE temp_str text DEFAULT '';	-- temporary string holder
  	DECLARE temp_val VARCHAR(255) DEFAULT '';	-- temporary string holder for query
 
	-- get length
	SET str_len=LENGTH(str);	
 
	SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1;	
		-- get total number delimeters and add 1
		-- add 1 since total separated values are 1 more than the number of delimiters
 
	-- start of while loop
	WHILE(ctr<i) DO
		-- add 1 to the counter, which will also be used to get the value of the string
		SET ctr=ctr+1; 
 
		-- get value separated by delimiter using ctr as the index
		SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '');
 
		-- query real value and insert into temporary value holder, temp_str contains the exploded ID    		
		SELECT ImageFileName INTO temp_val FROM custombu_roomassets_images WHERE ImageID=temp_str;
 
		-- concat real value into output string separated by delimiter
    		SET out_str=CONCAT(out_str, temp_val, ',');
	END WHILE;
	-- end of while loop
 
	-- trim delimiter from end of string
	SET out_str=TRIM(TRAILING delim FROM out_str);
	RETURN(out_str);	-- return 
 
END
  1.  BEGIN 
  2.      DECLARE i INT DEFAULT 0;    -- total number of delimiters 
  3.      DECLARE ctr INT DEFAULT 0;    -- counter for the loop 
  4.      DECLARE str_len INT;        -- string length,self explanatory 
  5.      DECLARE out_str text DEFAULT '';    -- return string holder 
  6.      DECLARE temp_str text DEFAULT '';    -- temporary string holder 
  7.        DECLARE temp_val VARCHAR(255) DEFAULT '';    -- temporary string holder for query 
  8.   
  9.      -- get length 
  10.      SET str_len=LENGTH(str)
  11.   
  12.      SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1
  13.          -- get total number delimeters and add 1 
  14.          -- add 1 since total separated values are 1 more than the number of delimiters 
  15.   
  16.      -- start of while loop 
  17.      WHILE(ctr<i) DO 
  18.          -- add 1 to the counter, which will also be used to get the value of the string 
  19.          SET ctr=ctr+1
  20.   
  21.          -- get value separated by delimiter using ctr as the index 
  22.          SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '')
  23.   
  24.          -- query real value and insert into temporary value holder, temp_str contains the exploded ID 
  25.          SELECT ImageFileName INTO temp_val FROM custombu_roomassets_images WHERE ImageID=temp_str; 
  26.   
  27.          -- concat real value into output string separated by delimiter 
  28.              SET out_str=CONCAT(out_str, temp_val, ',')
  29.      END WHILE; 
  30.      -- end of while loop 
  31.   
  32.      -- trim delimiter from end of string 
  33.      SET out_str=TRIM(TRAILING delim FROM out_str)
  34.      RETURN(out_str);    -- return 
  35.   
  36.  END 
Note: You need to change the words in red to the column, table and the ID field of the database values you'll be using. In my case, ImageFileName is the full name of each image; custombu_roomassets_images is the table containing my Image IDs and their filenames; ImageID is the ID of each image.

Previously
Here's what was happening before using this query without the function (example: c.RoomImages='1,2,3'):
copyraw
SELECT
	c.RoomName,
	(SELECT GROUP_CONCAT(i.ImageFilename) FROM custombu_roomassets_images i WHERE i.ImageID IN (c.RoomImages)) AS "ImageFiles"
FROM
	custombu_roomassets c
WHERE
	c.Display=1
ORDER BY
	c.RoomName


// Yielded:
RoomName	ImageFiles		
=============== ======================= 
B101		B101a.png		
B102		B102a.png
  1.  SELECT 
  2.      c.RoomName, 
  3.      (SELECT GROUP_CONCAT(i.ImageFilename) FROM custombu_roomassets_images i WHERE i.ImageID IN (c.RoomImages)) AS "ImageFiles" 
  4.  FROM 
  5.      custombu_roomassets c 
  6.  WHERE 
  7.      c.Display=1 
  8.  ORDER BY 
  9.      c.RoomName 
  10.   
  11.   
  12.  // Yielded: 
  13.  RoomName    ImageFiles 
  14.  =============== ======================= 
  15.  B101        B101a.png 
  16.  B102        B102a.png 

Now
Now here's the same query with the function (example: c.RoomImages='1,2,3'):
copyraw
SELECT
	c.RoomName,
	splitAndTranslate(c.RoomImages, ',') AS "ImageFiles"
FROM
	custombu_roomassets c
WHERE
	c.Display=1
ORDER BY
	c.RoomName


// Yielded:
RoomName	ImageFiles		
=============== =============================
B101		B101a.png,B101b.png,B101c.png
B102		B102a.png,B102b.png
  1.  SELECT 
  2.      c.RoomName, 
  3.      splitAndTranslate(c.RoomImages, ',') AS "ImageFiles" 
  4.  FROM 
  5.      custombu_roomassets c 
  6.  WHERE 
  7.      c.Display=1 
  8.  ORDER BY 
  9.      c.RoomName 
  10.   
  11.   
  12.  // Yielded: 
  13.  RoomName    ImageFiles 
  14.  =============== ============================= 
  15.  B101        B101a.png,B101b.png,B101c.png 
  16.  B102        B102a.png,B102b.png 

Done!

Other Searches
  • making the mysql where in comma separated string work
  • making the mysql where in work
  • mysql convert comma separated string into rows
  • mysql WHERE in comma separated string
Category: MySQL :: Article: 408

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.