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,'唐伯虎點秋香','喜劇、古裝、愛情');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|唐伯虎點秋香|愛情 |
