MySQL Procedure to get list items from comma-delimited string

drop table if exists splitstr;
CREATE TABLE `splitstr` (
`splititem` varchar(100)
);
select 
SUBSTRING_INDEX(
TRIM(LEADING ‘,’
FROM TRIM(LEADING
SUBSTRING_INDEX(‘apple,orange,banana,grapes,plum’,’,’,2) FROM
‘apple,orange,banana,grapes,plum’)
),
‘,’,1);
‘apple,orange,banana,grapes,plum’

First -get all the leading substring

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

Second — trim the above substr

Third — trim the leading comma

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);
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 ;

--

--

--

An artist , writer, web developer, who cares to share and clap at deep insights, humor, lateral thoughts and passionate pursuits.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Announcing Neo4j AuraDB Free

Using the Correct Versions in Your Package Manager

Malware Analysis of a Cryptocurrency Miner — Part 3

How to Run Apache Airflow using WSL without Docker on Windows

Quantum Computing: Understanding Simple Quantum Gates Using Qiskit

Monitoring Golang Web App with Application Insights

Reduce Cost and Increase Productivity with Value Added IT Services from buzinessware — {link} -

How to Master Excel in 11 Steps

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Patience Phillips

Patience Phillips

An artist , writer, web developer, who cares to share and clap at deep insights, humor, lateral thoughts and passionate pursuits.

More from Medium

Clone of Beardo.in including Frontend & Backend

Here are the steps to setting up an Express API

Creating First Smart Contract — Nothing Different From a Container

Top 5 Plugins for Revit