SQL表值函数之字符串拆分

今天我们来讨论一下字符串聚合的值函字符反操作，也就是串拆将单个字符串拆分成多行字符串。本文涉及的值函字符数据库包括 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)); INSERTINTO movies VALUES(1,千与千寻,动画、剧情、串拆奇幻); INSERTINTO movies VALUES(2,值函字符阿甘正传,剧情、爱情); INSERTINTO movies VALUES(3,串拆唐伯虎点秋香,喜剧、古装、值函字符爱情);1.2.3.4.5. Oracle

Oracle 没有提供拆分字符串的表值函数，我们可以创建一个自定义的 PL/SQL 函数来实现这个功能。首先，创建一个集合类型：

复制CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(4000);1.

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; ENDIF; ENDLOOP; END string_split;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.

string_split 函数可以将输入的字符串以指定分隔符进行拆分，默认分隔符为逗号。例如：

复制SELECT v.column_value FROM string_split(Oracle,MySQL,SQL Server,PostgreSQL,SQLit) v; COLUMN_VALUE| ------------| Oracle | MySQL | SQL Server | PostgreSQL | SQLit |1.2.3.4.5.6.7.8.9.10.

我们也可以将该函数应用到查询中的字段，源码库例如：

复制SELECT id, name, column_value FROM movies CROSSJOIN string_split(class,、); ID|NAME |COLUMN_VALUE| --|------------|------------| 1|千与千寻 |动画 | 1|千与千寻 |剧情 | 1|千与千寻 |奇幻 | 2|阿甘正传 |剧情 | 2|阿甘正传 |爱情 | 3|唐伯虎点秋香|喜剧 | 3|唐伯虎点秋香|古装 | 3|唐伯虎点秋香|爱情 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.

查询通过交叉连接将 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 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

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|唐伯虎点秋香|爱情 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.

其他数据库也都实现了通用表表达式，因此也可以使用这种方法进行字符串的拆分。

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 |1.2.3.4.5.6.7.8.9.10.

STRING_SPLIT 函数第一个参数是被拆分的字符串，第二个参数是拆分使用的分隔符。高防服务器函数返回一个单字段的表，字段名为“value” 。如果任何输入参数为 nvarchar 或 nchar 类型，则返回 nvarchar 类型；否则，返回 varchar 类型。返回类型的长度与字符串参数的长度相同。

以下查询使用 CROSS APPLY 将 class 字段进行了展开：

复制SELECT id, name,value FROM movies CROSSAPPLY string_split(class,、); id|name |value| --|------------|------| 1|千与千寻 |动画 | 1|千与千寻 |剧情 | 1|千与千寻 |奇幻 | 2|阿甘正传 |剧情 | 2|阿甘正传 |爱情 | 3|唐伯虎点秋香|喜剧 | 3|唐伯虎点秋香|古装 | 3|唐伯虎点秋香|爱情 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.

SQL Server 不能像 Oracle 那样直接使用连接查询。

如果想要查找剧情类的电影，可以在子查询中使用 string_split 函数：

复制SELECT id, name, class FROM movies WHEREEXISTS(SELECT1FROM string_split(class,、)WHEREvalue=剧情); id|name |class | --|-------|---------------| 1|千与千寻|动画、剧情、奇幻| 2|阿甘正传|剧情、爱情 |1.2.3.4.5.6.7.8. PostgreSQL

首先，PostgreSQL 中所有的函数实际上都可以作为表值函数使用。例如：

复制SELECT*FROM abs(10); abs| ---| 10|1.2.3.4.5.

我们知道，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 |1.2.3.4.5.6.7.8.9.10.

以下查询使用 CROSS JOIN 将 class 字段进行了展开：

复制SELECT* FROM movies CROSSJOIN regexp_split_to_table(class,、) v; id|name |class |v | --|------------|--------------|---| 1|千与千寻 |动画、剧情、奇幻|动画| 1|千与千寻 |动画、源码下载剧情、奇幻|剧情| 1|千与千寻 |动画、剧情、奇幻|奇幻| 2|阿甘正传 |剧情、爱情 |剧情| 2|阿甘正传 |剧情、爱情 |爱情| 3|唐伯虎点秋香|喜剧、古装、爱情|喜剧| 3|唐伯虎点秋香|喜剧、古装、爱情|古装| 3|唐伯虎点秋香|喜剧、古装、爱情|爱情|1.2.3.4.5.6.7.8.9.10.11.12.13.14.

想一想，怎么查找剧情类的电影？

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 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

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|唐伯虎点秋香|爱情 |1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.