SQL: how to split a stringSQL: how to split a string-CodePudding

I want to split a character string is part of the comma but the orca I try my code below it only returns me the index of the first comma and not the different strings fraction of the sentence

DELIMITER $$
create procedure separertext()
BEGIN

DECLARE text varchar (128);
DECLARE i varchar (10);
DECLARE j varchar(10);
DECLARE ind varchar(100);
DECLARE nom varchar (128);
set text = 'bonjour,daryle,manuella';
select  LOCATE(',', text) as c;
SELECT SUBSTRING(text, 1, c) AS ExtractString;
END$$
DELIMITER ;

and here is the result I got

 ------ 
| c    |
 ------ 
|    8 |
 ------ `
`1 row in set (0.001 sec)

CodePudding user response：

You might look at the SUBSTRING_INDEX function of MySQL for your procedure.

Here is a good tutorial to help you with your Problem.

CodePudding user response：

You need an iterative approach to extract an unknown number of substrings from a string. In SQL this is done with recursive queries (available since MySQL 8.0).

I am using REGEXP_SUBSTR here for convenience. The same can be done with a combination of SUBSTRING and LOCATE.

with recursive substrings(str, substr, pos) as
(
  select str, substring_index(str, ',', 1), 1
  from mytable
  union all
  select str, regexp_substr(str, '[^,] ', 1, pos   1), pos   1
  from substrings
  where regexp_substr(str, '[^,] ', 1, pos   1) is not null
)
select * 
from substrings
order by str, pos;

Demo: https://dbfiddle.uk/yRv4fUD1