个人中心
退出
SQL 表值函数之字符串拆分
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
表值函数
Oracle
MySQL
SQL Server
PostgreSQL
SQLite
总结
大家好，我是只谈技术不剪发的 Tony 老师。
在前面的文章中我们介绍了如何通过 SQL 聚合函数（LISTAGG、STRING_AGG、GROUP_CONCAT）将多行字符串合并成单个字符串。今天我们来讨论一下字符串聚合的反操作，也就是将单个字符串拆分成多行字符串。本文涉及的数据库包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。
如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
表值函数
表值函数（Table-Valued Function）是指返回结果是一个表或者集合的函数，也称为行集函数（Set Returning Function）。表值函数可以当作一个数据表在查询中使用，类似于子查询或者视图。在文章中我们会使用到以下示例表：
CREATE TABLE movies(id int primary key, name varchar(50), class varchar(200));
INSERT INTO movies VALUES (1, '千与千寻', '动画、剧情、奇幻');
INSERT INTO movies VALUES (2, '阿甘正传', '剧情、爱情');
INSERT INTO movies VALUES (3, '唐伯虎点秋香', '喜剧、古装、爱情');
Oracle
Oracle 没有提供拆分字符串的表值函数，我们可以创建一个自定义的 PL/SQL 函数来实现这个功能。首先，创建一个集合类型：
CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(4000);
str_list 可以看做一个由字符串数据组成的数组或者列表。然后创建一个拆分字符串的函数：
CREATE OR REPLACE FUNCTION string_split(p_str IN VARCHAR2, p_sep IN VARCHAR2 := ',')
RETURN str_list pipelined
IS
ln_idx PLS_INTEGER;
lv_list VARCHAR2(4000) := p_str;
BEGIN
LOOP
ln_idx := INSTR(lv_list, p_sep);
IF ln_idx > 0 THEN
pipe ROW(SUBSTR(lv_list, 1, ln_idx - 1));
lv_list := SUBSTR(lv_list, ln_idx + LENGTH(p_sep));
ELSE
pipe ROW(lv_list);
EXIT;
END IF;
END LOOP;
END string_split;
string_split 函数可以将输入的字符串以指定分隔符进行拆分，默认分隔符为逗号。例如：
SELECT v.column_value
FROM string_split('Oracle,MySQL,SQL Server,PostgreSQL,SQLit') v;
COLUMN_VALUE|
------------|
Oracle |
MySQL |
SQL Server |
PostgreSQL |
SQLit |
我们也可以将该函数应用到查询中的字段，例如：
SELECT id, name, column_value
FROM movies
CROSS JOIN string_split(class, '、');
ID|NAME |COLUMN_VALUE|
--|------------|------------|
1|千与千寻 |动画 |
1|千与千寻 |剧情 |
1|千与千寻 |奇幻 |
2|阿甘正传 |剧情 |
2|阿甘正传 |爱情 |
3|唐伯虎点秋香|喜剧 |
3|唐伯虎点秋香|古装 |
3|唐伯虎点秋香|爱情 |
查询通过交叉连接将 class 字段中的数据进行了展开。
想一想，怎么查找剧情类的电影？
MySQL
MySQL 没有提供拆分字符串的表值函数，也不支持自定义函数来实现这个功能。不过，我们可以利用递归通用表表达式来实现字符串的拆分：
WITH RECURSIVE t(sub, str) AS (
SELECT concat('Oracle,MySQL,SQL Server,PostgreSQL,SQLite',','), concat('Oracle,MySQL,SQL Server,PostgreSQL,SQLite',',')
UNION ALL
SELECT substr(str, 1, instr(str, ',')-1), substr(str, instr(str, ',')+1)
FROM t WHERE instr(str, ',')>0
)
SELECT sub
FROM t WHERE instr(sub, ',') = 0;
sub |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLite |
WITH RECURSIVE 表示递归通用表表达式，每次递归都返回一个拆分后的子串。将上面的查询应用到 movies 表中可以将电影的类型进行展开：
WITH RECURSIVE t(id, name, sub, str) AS (
SELECT id, name, concat(class,'、'), concat(class,'、')
FROM movies
UNION ALL
SELECT id, name,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
FROM t WHERE instr(str, '、')>0
)
SELECT id, name, sub
FROM t WHERE instr(sub, '、') = 0;
id|name |sub |
--|------------|----|
1|千与千寻 |动画 |
2|阿甘正传 |剧情 |
3|唐伯虎点秋香|喜剧 |
1|千与千寻 |剧情 |
2|阿甘正传 |爱情 |
3|唐伯虎点秋香|古装 |
1|千与千寻 |奇幻 |
3|唐伯虎点秋香|爱情 |
其他数据库也都实现了通用表表达式，因此也可以使用这种方法进行字符串的拆分。
SQL Server
SQL Server 2016 引入了一个字符串表值函数 STRING_SPLIT，它可以根据指定的分隔符将字符串拆分为子字符串行。例如：
SELECT v.value
FROM string_split('Oracle,MySQL,SQL Server,PostgreSQL,SQLit', ',') v;
value |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLit |
STRING_SPLIT 函数第一个参数是被拆分的字符串，第二个参数是拆分使用的分隔符。函数返回一个单字段的表，字段名为“value” 。如果任何输入参数为 nvarchar 或 nchar 类型，则返回 nvarchar 类型；否则，返回 varchar 类型。 返回类型的长度与字符串参数的长度相同。
以下查询使用 CROSS APPLY 将 class 字段进行了展开：
SELECT id, name, value
FROM movies
CROSS APPLY string_split(class, '、');
id|name |value |
--|------------|------|
1|千与千寻 |动画 |
1|千与千寻 |剧情 |
1|千与千寻 |奇幻 |
2|阿甘正传 |剧情 |
2|阿甘正传 |爱情 |
3|唐伯虎点秋香|喜剧 |
3|唐伯虎点秋香|古装 |
3|唐伯虎点秋香|爱情 |
SQL Server 不能像 Oracle 那样直接使用连接查询。
如果想要查找剧情类的电影，可以在子查询中使用 string_split 函数：
SELECT id, name, class
FROM movies
WHERE EXISTS (SELECT 1 FROM string_split(class, '、') WHERE value = '剧情');
id|name |class |
--|-------|---------------|
1|千与千寻|动画、剧情、奇幻|
2|阿甘正传|剧情、爱情 |
PostgreSQL
首先，PostgreSQL 中所有的函数实际上都可以作为表值函数使用。例如：
SELECT * FROM abs(10);
abs|
---|
10|
我们知道，FROM 子句后面就是表，因此 ABS 函数的返回结果可以看做一个一行一列的表。
PostgreSQL 提供了一个拆分字符串的函数 regexp_split_to_table ，可以通过一个 POSIX 正则表达式指定分隔符。例如：
SELECT *
FROM regexp_split_to_table('Oracle,MySQL,SQL Server,PostgreSQL,SQLit', ',') v;
v |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLit |
以下查询使用 CROSS JOIN 将 class 字段进行了展开：
SELECT *
FROM movies
CROSS JOIN regexp_split_to_table(class, '、') v;
id|name |class |v |
--|------------|--------------|---|
1|千与千寻 |动画、剧情、奇幻|动画|
1|千与千寻 |动画、剧情、奇幻|剧情|
1|千与千寻 |动画、剧情、奇幻|奇幻|
2|阿甘正传 |剧情、爱情 |剧情|
2|阿甘正传 |剧情、爱情 |爱情|
3|唐伯虎点秋香|喜剧、古装、爱情|喜剧|
3|唐伯虎点秋香|喜剧、古装、爱情|古装|
3|唐伯虎点秋香|喜剧、古装、爱情|爱情|
想一想，怎么查找剧情类的电影？
SQLite
SQLite 没有提供拆分字符串的表值函数，也不支持自定义函数来实现这个功能。不过，我们可以像 MySQL 一样利用递归通用表表达式来实现字符串的拆分：
WITH RECURSIVE t(sub, str) AS (
SELECT '', 'Oracle,MySQL,SQL Server,PostgreSQL,SQLite'||','
UNION ALL
SELECT substr(str, 1, instr(str, ',')-1), substr(str, instr(str, ',')+1)
FROM t WHERE instr(str, ',')>0
)
SELECT sub
FROM t WHERE sub != '';
sub |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLite |
WITH RECURSIVE 表示递归通用表表达式，每次递归都返回一个拆分后的子串。将上面的查询应用到 movies 表中可以将电影的类型进行展开：
WITH RECURSIVE t(id, name, sub, str) AS (
SELECT id, name, '', class||'、'
FROM movies
UNION ALL
SELECT id, name,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
FROM t WHERE instr(str, '、')>0
)
SELECT id, name, sub
FROM t WHERE sub != '';
id|name |sub |
--|------------|----|
1|千与千寻 |动画 |
2|阿甘正传 |剧情 |
3|唐伯虎点秋香|喜剧 |
1|千与千寻 |剧情 |
2|阿甘正传 |爱情 |
3|唐伯虎点秋香|古装 |
1|千与千寻 |奇幻 |
3|唐伯虎点秋香|爱情 |
总结
本文介绍了如何利用表值函数将单个字符串拆分为多行字符串，其中 SQL Server 和 PostgreSQL 提供了内置的函数，Oracle 可以创建自定义 PL/SQL 函数，MySQL 和 SQLite 可以利用通用表表达式实现。
分类导航