How to split a comma-separated string into multiple lines

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何将逗号分隔开的字符串，拆分成多行

| username: 大飞飞呀

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
For example, the field contains Xiaoming, Xiaohong
How to split into multiple rows
Return
Xiaohong
Xiaoming
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

2
| username: tidb狂热爱好者 | Original post link

MySQL has functions.

3
| username: 大飞飞呀 | Original post link

Does TiDB have it?

4
| username: 啦啦啦啦啦 | Original post link

Isn’t it better to let the program handle it?

5
| username: tidb菜鸟一只 | Original post link

CREATE TABLE USER (NAME VARCHAR(200));

INSERT INTO USER(NAME) VALUES(‘Zhang San, Li Si, Wang Wu’);

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ‘,’, b.help_topic_id + 1), ‘,’, -1) AS NAME
FROM USER a, (SELECT 0 help_topic_id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) b
WHERE b.help_topic_id < (LENGTH(a.name) - LENGTH(REPLACE(a.name, ‘,’, ‘’)) + 1);

6
| username: Jellybean | Original post link

Typically, row-to-column transformation can be achieved using group_concat;

Column-to-row transformation can be indirectly achieved using union all.

For example:
If you have a string “a,b,c” that needs to be split into multiple rows, you can perform the following operations.

  1. First step, implement the split function (using the SUBSTRING_INDEX function):
mysql> select SUBSTRING_INDEX('a,b,c',',',1),SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c',',',2),',',-1),SUBSTRING_INDEX('a,b,c',',',-1);
+--------------------------------+--------------------------------------------------------+---------------------------------+
| SUBSTRING_INDEX('a,b,c',',',1) | SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c',',',2),',',-1) | SUBSTRING_INDEX('a,b,c',',',-1) |
+--------------------------------+--------------------------------------------------------+---------------------------------+
| a                              | b                                                      | c                               |
+--------------------------------+--------------------------------------------------------+---------------------------------+
1 row in set (0.00 sec)
  1. Second step, column-to-row transformation (using union):
mysql> select SUBSTRING_INDEX('a,b,c',',',1) union select SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c',',',2),',',-1) union select SUBSTRING_INDEX('a,b,c',',',-1);
+--------------------------------+
| SUBSTRING_INDEX('a,b,c',',',1) |
+--------------------------------+
| c                              |
| b                              |
| a                              |
+--------------------------------+
3 rows in set (0.01 sec)
7
| username: Kongdom | Original post link

The person above is correct, but I don’t understand why you want to split it into multiple lines.

8
| username: liuis | Original post link

The database resources themselves are expensive, and handling these database functions is inherently slower than processing them in the program. It’s much better to let the program code handle it…

9
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.