字符串分割并展开成表格的SQL实现方法

一、场景案例

在数据开发类项目中，常常会遇到需要将字符串进行切割并展开成表格的场景，如以下两种常见的案例：

• 标签类型数据：如将员工标签类型的数据
  1. - 技能：Java、Spring、微服务、项目管理
  2. - 项目经验：电商、金融、教育<br>- 工作风格：#执行力强 #跨部门协作 #数据驱动

  复制代码
• 分类层级数据：如将组织部门层级数据：“/集团/分公司/信息技术部/数据团队”
  

除此之外，在一些特定的业务场景中，如证券场景中，需要将一笔回购合约涉及到的多个质押券进行拆分（提取每个质押券有质押数量及对应标准券转换比例），数据示例如下：

 另外，API请求的返回的数据多为半结构化的字典列表数据，需要将每行数据从列表中进行提取出来再进行后续的处理，如以下数据：

1. {"data": [<br>　　 {"AMOUNT":-9000,"SUB_TYPE_NAME":"债券分销","BRANCH_NAME":"银行间","TYPE_NAME":"收入","BUSI_DATE":"20250831","PROJECT_NAME":"调整1月分销佣金-24****MTN001"},
2.     {"AMOUNT":-1075.61,"SUB_TYPE_NAME":"债券分销","BRANCH_NAME":"银行间","TYPE_NAME":"收入","BUSI_DATE":"20250531","PROJECT_NAME":"调整计提1月及3月分销佣金"}
3.     ],
4. "success":true
5. }

复制代码

二，各类主流SQL方言实现方法

针对这类场景，目前没有标准SQL函数支持实现该功能。各类SQL方言通过自定义方法进行支持，以下是常用的实现方法。
Spark/Hive SQL

采用split + explode（及升级版posexplode）。以下为测试案例：

 利用该方法可以轻松的回购质押券进行拆分处理：

PostgreSQL

有两种方法：方法一：首先利用string_to_array将字符串切割成数组，然后利用unnest将数组扩展成表

方法二：直接利用regexp_split_to_table将字符串切割并转换成表

1. select string_to_array('apple,banana,orange', ',') as item;
2. select string_to_array('apple|banana|orange', '|') as item;
3. select unnest(string_to_array('apple,banana,orange', ',')) as item;
5. select regexp_split_to_array('apple|banana|orange', '\|') as item;
6. select regexp_split_to_table('apple|banana|orange', '\|') as item;

复制代码

View CodeOracle

利用connect by + Level来实现

1. WITH CTE_DATA AS (
2.     SELECT 'tom' AS NAME, 'apple,banana,cherry' AS FRUIT FROM DUAL
3. )
4. SELECT NAME, REGEXP_SUBSTR(FRUIT, '[^,]+', 1, LEVEL) AS FRUIT
5. FROM CTE_DATA
6. CONNECT BY REGEXP_SUBSTR(FRUIT, '[^,]+', 1, LEVEL) IS NOT NULL
7. ;

复制代码

View CodeMySQL

5.x+版本只能使用数字辅助表+SUBSTRING_INDEX。

1. CREATE TABLE IF NOT EXISTS numbers (n INT PRIMARY KEY);
2. INSERT INTO numbers
3. SELECT a.N + b.N * 10 + c.N * 100 + 1 AS n
4. FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
5. CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
6. CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c;
7. -- 使用数字表分割
8. SELECT
9. SUBSTRING_INDEX(
10. SUBSTRING_INDEX('a,b,c,d', ',', numbers.n),
11. ',',
12. -1
13. ) AS part
14. FROM numbers
15. WHERE numbers.n <= LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1;

复制代码

View Code

1. SELECT
2.     o.order_id,
3.     j.product_id
4. FROM orders o
5. JOIN JSON_TABLE(
6.     CONCAT('["', REPLACE(o.product_ids, ',', '","'), '"]'),
7.     '$[*]' COLUMNS (product_id INT PATH '$')
8. ) AS j;

复制代码

View Code2016之后的版本可使用原生方法STRING_SPLIT

1. CREATE FUNCTION dbo.SplitString
2. (
3.     @String NVARCHAR(MAX),
4.     @Delimiter CHAR(1)
5. )
6. RETURNS @Results TABLE
7. (
8.     ID INT IDENTITY(1,1),
9.     Value NVARCHAR(MAX)
10. )
11. AS
12. BEGIN
13.     DECLARE @pos INT = 0
14.     DECLARE @slice NVARCHAR(MAX)
16.     IF RIGHT(@String, 1) != @Delimiter
17.         SET @String = @String + @Delimiter
19.     WHILE CHARINDEX(@Delimiter, @String) > 0
20.     BEGIN
21.         SET @pos = CHARINDEX(@Delimiter, @String)
22.         SET @slice = LEFT(@String, @pos - 1)
23.         
24.         INSERT INTO @Results (Value) VALUES (@slice)
25.         SET @String = STUFF(@String, 1, @pos, '')
26.     END
27.    
28.     RETURN
29. END
31. -- 使用示例
32. SELECT * FROM dbo.SplitString('张三,李四,王五', ',')

复制代码

三、结论

　　Spark SQL、PostgreSQL最方便，思路也比较相似，都是先将字符串拆分数组，然后扩展成表。SQLServer新版本实现较为方法，直接一个函数搞定。Oracle利用其connect by来实现，也相对便捷，只是相对主流SQL方言比较小众一点。Mysql新版本相对方便一点，老版本需要辅助表增加维护成本。四、参考链接

https://www.kimi.com/share/19bd8855-80d2-8885-8000-00003449be16https://www.kimi.com/share/19bd8885-e842-8472-8000-000042aa28d0https://chat.deepseek.com/share/h9bv2i75c90jcfz3uohttp://blog.itpub.net/16436858/viewspace-624191/  
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

不错，里面软件多更新就更好了

东西不错很实用谢谢分享

热心回复！

谢谢分享，辛苦了