SQL表值函数之字符串拆分

数据库 SQL Server
今天我们来讨论一下字符串聚合的反操作,也就是将单个字符串拆分成多行字符串。本文涉及的数据库包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。

今天我们来讨论一下字符串聚合的反操作,也就是将单个字符串拆分成多行字符串。本文涉及的数据库包括 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,'唐伯虎点秋香','喜剧、古装、爱情');

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;
     ENDIF;
  ENDLOOP;
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  
CROSSJOIN 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  
CROSSAPPLY 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  
WHEREEXISTS(SELECT1FROM string_split(class,'、')WHEREvalue='剧情');

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
CROSSJOIN 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编程思想
相关推荐

2011-03-22 10:44:20

SQL Server数拆分字符串函数

2010-09-09 11:48:00

SQL函数字符串

2010-11-08 17:07:41

SQL Server字

2010-07-14 16:37:33

SQL Server拆

2010-09-06 17:30:46

SQL函数

2010-06-28 15:18:51

SQL Server

2010-09-13 14:55:09

sql server字

2014-01-02 16:14:10

PostgreSQL字符串

2021-03-14 15:07:55

SQLServer数据库字符串

2010-09-06 17:26:54

SQL函数

2010-03-12 18:29:56

Python字符串替换

2010-10-21 15:26:35

SQL Server字

2010-10-09 11:54:46

MySQL字符串

2009-11-24 09:55:44

PHP字符串函数

2010-09-13 15:12:26

sql server字

2021-11-24 09:08:38

LeetCode字符串算法

2009-08-06 16:01:09

C#字符串函数大全

2010-07-14 16:35:52

Perl字符串处理函数

2010-11-26 10:14:40

MySQL repla

2010-05-21 17:22:22

点赞
收藏

51CTO技术栈公众号