MySQL Procedure to get list items from comma-delimited string

3 min readNov 29, 2020

Get the items delimited by comma, load them into a DB table and use them further

Often times, at the DB end we might get a comma delimited string for some good reason of the all the tiers upstream decided to deliver it that way ! They might be strings, numbers but just delimited by comma.

At DB end we might need to run through the list and get the items for further processing. DBs offer string functions which can be harnessed to achieve this, over a loop and load them to a table, then it gets much easier to run through this table and go ahead.

All required is a table, for this articles purpose I am using a real table, but it can be done with a temporary table too. splitstr is the table below, with a single column splititem, column size can be changed suitably

drop table if exists splitstr;
CREATE TABLE `splitstr` (
 `splititem` varchar(100) 
);

All the splitting is done by putting together a couple of string functions. Then looping it over the no. of commas in the input string.

First lets look at the the underlying select and take it apart.

select 
SUBSTRING_INDEX(
 TRIM(LEADING ‘,’ 
 FROM TRIM(LEADING 
 SUBSTRING_INDEX(‘apple,orange,banana,grapes,plum’,’,’,2) FROM 
 ‘apple,orange,banana,grapes,plum’)
 ),
‘,’,1);

The breakup of the above select is as below. Lets say the comma string is as below and we need to get banana

‘apple,orange,banana,grapes,plum’

First -get all the leading substring

Lets start with the inner most select, this select is just to trim off string till the 2nd coma, since we want banana its the 3rd item in the list

select SUBSTRING_INDEX(‘apple,orange,banana,grapes,plum’,’,’,2);

Second — trim the above substr

The next encompassing function is trim the above ‘apple, orange’ and leave the rest of the string

Third — trim the leading comma

The next level function is to take of the leading comma in the ‘,banana,grapes,plum’, our objective is to get only the banana

select TRIM(LEADING ‘,’ 
 FROM TRIM(LEADING SUBSTRING_INDEX(‘apple,orange,banana,grapes,plum’,’,’,2) FROM 
 ‘apple,orange,banana,grapes,plum’));

Fourth — substr till the next comma

select 
SUBSTRING_INDEX(
 TRIM(LEADING ‘,’ 
 FROM TRIM(LEADING 
 SUBSTRING_INDEX(‘apple,orange,banana,grapes,plum’,’,’,2) FROM 
 ‘apple,orange,banana,grapes,plum’)
 ),
‘,’,1);

Our objective is to go over all the items, with the above select and this is accomplished with a loop, with no. of commas as the end point. Loop index every time serves as the item index in the list.

The procedure is below

drop procedure if exists splitcommastring;
DELIMITER $$
CREATE DEFINER=`yourdb`@`localhost` PROCEDURE `splitcommastring`(IN in_commastring VARCHAR(255))
BEGIN
 DECLARE temptext varchar(100);
 declare noOfComas int(11); 
 declare loopidx int(11);
 
 delete from splitstr;
 set loopidx = 0;
 select LENGTH(in_commastring) — LENGTH(REPLACE(in_commastring, ‘,’, ‘’)) into noOfComas;
 
do_this: LOOP
 IF loopidx > noOfComas THEN 
 LEAVE do_this;
 END IF;
 select SUBSTRING_INDEX(
 TRIM(LEADING ‘,’ FROM TRIM(LEADING SUBSTRING_INDEX(in_commastring,’,’,loopidx) FROM in_commastring)),
 ‘,’,1) into temptext;
 insert into splitstr values (temptext);set loopidx = loopidx + 1;
 END LOOP do_this;select * from splitstr;
END$$
DELIMITER ;