Getting the mysql where in delimited string to work

: Joel Lipman; MySQL; 02 January 2012; Hits: 8153

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:

copy raw

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
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:

copy raw

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

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

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'):

copy raw

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

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

Now
Now here's the same query with the function (example: c.RoomImages='1,2,3'):

copy raw

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

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

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