divide single line to multiple rows without using while loop in mysql

Question

I have the string like '1.1;1.6;1.4;1.9;1.12...'

I want to split the above string to rows based on the substring ';' like below in mysql without using while loop:

1.1
1.6
1.4
1.9
1.12
...

Possible duplicate of ["Reverse GROUP\_CONCAT" in MySQL?](https://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql) — Utsav, Aug 08 '17 at 14:42
thank you for your immediate reply. but i need the result without using while loop. — Arun manthiram, Aug 08 '17 at 14:45

score 2 · Answer 1 · answered Aug 08 '17 at 15:39

I have a table of integers (i), with values (0-9)...

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX('1.1;1.6;1.4;1.9;1.12',';',i+1),';',-1) x
           FROM ints x;
+------+
| x    |
+------+
| 1.1  |
| 1.6  |
| 1.4  |
| 1.9  |
| 1.12 |
+------+

This solution is essentially identical to that provided by fthiella at the link provided by Utsav

score 1 · Accepted Answer · answered Aug 08 '17 at 15:27

1

mysql does not have a native split function. you will either have to use a loop/cursor or create your own string splitting function using the documentation

https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

answered Aug 08 '17 at 15:27

ttallierchio

460
7
17

divide single line to multiple rows without using while loop in mysql

2 Answers2