Split string values separated by a semicolon using sProc in MySQL 8-CodePudding

I need split this string using stored Procedure in MySQL 8 version

"John;Elizabeth;Mark;Zagor;Annie;Lucy;Peter;Robin;Wilson;Tom;Bettie;Myriam;Frankie;Nick;Marilyn"

The string values are separated by a semicolon.

My sProc below.

The problem it's in output.

The first name splitted on this string it's Elizabeth and not John.

Where is it John?

All other names are present in output of sProc, only John is missing...

What am I doing wrong?

BEGIN

DECLARE tNameSeries LONGTEXT;
DECLARE t_tNameSeries LONGTEXT;

SET tNameSeries = "John;Elizabeth;Mark;Zagor;Annie;Lucy;Peter;Robin;Wilson;Tom;Bettie;Myriam;Frankie;Nick;Marilyn";

WHILE LOCATE(";",tNameSeries) > 0 DO

SET tNameSeries = REPLACE (tNameSeries, (SELECT LEFT(tNameSeries,LOCATE(";",tNameSeries))),'');

SET t_tNameSeries = SUBSTRING_INDEX(tNameSeries,";",1);

SELECT t_tNameSeries;

END WHILE;

END

update

Using ths edit sProc the output is only John

BEGIN

DECLARE tNameSeries LONGTEXT;
DECLARE t_tNameSeries LONGTEXT;

SET tNameSeries = "John;Elizabeth;Mark;Zagor;Annie;Lucy;Peter;Robin;Wilson;Tom;Bettie;Myriam;Frankie;Nick;Marilyn";

WHILE LOCATE(";",tNameSeries) > 0 DO

SET t_tNameSeries = SUBSTRING_INDEX(tNameSeries,";",1);

SET tNameSeries = REPLACE (t_tNameSeries, (SELECT LEFT(t_tNameSeries,LOCATE(";",t_tNameSeries))),'');

SELECT tNameSeries;

END WHILE;

END

CodePudding user response：

SELECT *
FROM JSON_TABLE(
    CONCAT(
        '["',
        REPLACE(
            "John;Elizabeth;Mark;Zagor;Annie;Lucy;Peter;Robin;Wilson;Tom;Bettie;Myriam;Frankie;Nick;Marilyn",
            ';',
            '","'
            ),
        '"]'
        ),
    '$[*]' COLUMNS (
        id FOR ORDINALITY,
        name VARCHAR(255) PATH '$'
        )
    ) jsontable;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=546907fc5c00b7173fa73327fdd97638

Insert it into the SP if needed.

CodePudding user response：

This works too:


set @names = 'John;Elizabeth;Mark;Zagor;Annie;Lucy;Peter;Robin;Wilson;Tom;Bettie;Myriam;Frankie;Nick;Marilyn';

select 
   substring_index(substring_index(@names,';',R),';',-1) W 
from (select row_number() over () as R 
      from information_schema.tables) x 
where x.R<=1 length(@names)-length(replace(@names,';',''));

see: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5cc442be9da54d8cbcdbabc58ee37b65