Question 22210 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL: how to split a stringSQL: how to split a string – Mysql

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)
Tags:

2

Answers


  1. 0

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

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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search