Split a MYSQL string from GROUP_CONCAT into an ( array, like, expression, list) that IN () can understand
Instead of using IN()
, would using FIND_IN_SET()
be an option too?
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
Here's a full example based on the example problem in the question, confirmed as tested by the asker in an earlier edit to the question:
SELECT name FROM person LEFT JOIN tag ON person.id = tag.person_id GROUP BY person.id
HAVING ( FIND_IN_SET(1, GROUP_CONCAT(tag.tag_id)) ) AND ( FIND_IN_SET(2, GROUP_CONCAT(tag.tag_id)) );
+------+
| name |
+------+
| Bob |
+------+
You can pass a string as array, using a split separator, and explode it in a function, that will work with the results.
For a trivial example, if you have a string array like this: 'one|two|tree|four|five', and want to know if two is in the array, you can do this way:
create function str_in_array( split_index varchar(10), arr_str varchar(200), compares varchar(20) )
returns boolean
begin
declare resp boolean default 0;
declare arr_data varchar(20);
-- While the string is not empty
while( length( arr_str ) > 0 ) do
-- if the split index is in the string
if( locate( split_index, arr_str ) ) then
-- get the last data in the string
set arr_data = ( select substring_index(arr_str, split_index, -1) );
-- remove the last data in the string
set arr_str = ( select
replace(arr_str,
concat(split_index,
substring_index(arr_str, split_index, -1)
)
,'')
);
-- if the split index is not in the string
else
-- get the unique data in the string
set arr_data = arr_str;
-- empties the string
set arr_str = '';
end if;
-- in this trivial example, it returns if a string is in the array
if arr_data = compares then
set resp = 1;
end if;
end while;
return resp;
end
|
delimiter ;
I want to create a set of usefull mysql functions to work with this method. Anyone interested please contact me.
For more examples, visit http://blog.idealmind.com.br/mysql/how-to-use-string-as-array-in-mysql-and-work-with/