How to split comma separated text in MySQL stored procedure

Question

How to split comma separated text (list of IDs) in MySQL stored procedure to use result in SQL "IN" statement.

SELECT * FROM table WHERE table.id IN (splitStringFunction(commaSeparatedData, ','));

Answer 1

This is simple as hell for MySQL:

SELECT * FROM table WHERE FIND_IN_SET(table.id, commaSeparatedData);

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Answer 2

You could use a prepared statement inside the stored procedure to achieve this. You can create the whole select query as a string inside a variable and then concatenate in the comma delimited string into its IN clause. Then you can make a prepared statement from the query string variable and execute it.

DELIMITER ;;
create procedure testProc(in listString varchar(255))

BEGIN

set @query = concat('select * from testTable where id in (',listString,');');
prepare sql_query from @query;
execute sql_query;

END
;;

DELIMITER ;

call testProc("1,2,3");

Answer 3

You could try this MySql example. Before you use it, put some type safety checks in there (i.e. check id is integer, or match against regular expression before insert).

 # BEGIN split statements ids
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current CHAR DEFAULT '';
 DECLARE current_id VARCHAR(100) DEFAULT '';;
 CREATE TEMPORARY TABLE ids (`id` VARCHAR(100));
 split_ids: LOOP
  SET current = MID(statement_ids, current_pos, 1);
  IF (current_pos = LENGTH(statement_ids)) THEN
   IF current != delim THEN SET current_id = CONCAT(current_id,current); END IF;
   INSERT INTO ids(id) VALUES (current_id);
   LEAVE split_ids;
  END IF;
  IF current = delim THEN
   INSERT INTO ids(id) VALUES (current_id);
   SET current_id = '';
  ELSE
   SET current_id = CONCAT(current_id,current);
  END IF;
  SET current_pos = current_pos+1;
 END LOOP split_ids;
 # END split statement ids

 # to check ids are correct
 SELECT * FROM ids;

 # to use the ids:
 SELECT * FROM statements WHERE id IN (SELECT id FROM ids);

Answer 4

You can do it two ways:

1. SQL Library
2. Natively with REGEXP

Answer 5

OK, slightly "easier" but less geeky way for people like me:

say you have one table 'combined_city_state' which looks like:

'Chicago, Illinois'

copy that to 2 other tables:

CREATE TABLE city LIKE combined_city_state;
INSERT city SELECT * FROM combined_city_state;

CREATE TABLE state LIKE combined_city_state;
INSERT state SELECT * FROM combined_city_state;

You now have 3 tables with the same data as 'combined_city_state'.

Install this function:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Then apply this to each table to remove the extra index of data:

UPDATE firms 
SET city = (SELECT SPLIT_STR((city), ',', 1));


UPDATE firms 
SET state = (SELECT SPLIT_STR((state), ',', 2));

This leaves you with one column of just cities, one of just states. You can now remove the original 'combined_city_state' column if you don't need anymore.

Answer 6

You can use find_in_set() function for collection filter

how-to-split-and-search-in-comma-separated-values-in-mysql

SELECT * FROM table WHERE find_in_set(table.id,commaSeparatedData) > 0;

Answer 7

I'm surprised this one-liner isn't properly mentioned here:

SELECT * FROM table
WHERE id in (SELECT convert(int,Value) FROM dbo.Split(@list_string,',')

All you need is a Split SQL function like the one below which will come in handy in other ways as well:

CREATE FUNCTION dbo.Split
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(

    Id int identity(1,1),
    Value nvarchar(100)
) 
AS  
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
    Insert Into @RtnValue (value)
    Select 
        Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return
END

Answer 8

I have parsed data with hyphens in it. The example below uses a fixed text string to demonstrate, just change the references to relevant column names in the table. I played for ages with a way to ensure it worked on codes with varying numbers of components and in the end decided to add the where clause. Most data you are trying to parse would have a fixed number of columns.

select
SUBSTRING_INDEX(TS,"-",1) as "1",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",2)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",2)))-1)) as "2",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",3)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",3)))-1)) as "3",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",4)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",4)))-1)) as "4",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",5)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",5)))-1)) as "5",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",6)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",6)))-1)) as "6",reverse(left(reverse(SUBSTRING_INDEX(TS,"-",7)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",7)))-1)) as "7",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",8)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",8)))-1)) as "8",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",9)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",9)))-1)) as "9",
reverse(left(reverse(SUBSTRING_INDEX(TS,"-",10)),locate("-",reverse(SUBSTRING_INDEX(TS,"-",10)))-1)) as "10"
from (select "aaa-bbb-ccc-ddd-eee-fff-ggg-hhh-iii-jjj" as TS) as S
where (LENGTH(TS)-LENGTH(REPLACE(TS,'-',''))) =9

Answer 9

A bit strange but:

SET @i = 1;
set @str = 'a,b,c,d,e,f,g,h';

select temp.length into @length from 
(select
        ROUND(   
            (
                LENGTH(dt.data)
                - LENGTH( REPLACE (dt.data, ",", "") ) 
            ) / LENGTH(",")        
        )+1 AS length   
     from (select @str as data) dt
 ) temp;

SET @query = CONCAT('select substring_index(
    substring_index(@str, '','', seq), 
    '','', 
    -1
  ) as letter from seq_', @i, '_to_',@length);

PREPARE q FROM @query;
EXECUTE q;