個人中心
退出
SQL 表值函數(shù)之字符串拆分
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
表值函數(shù)
Oracle
MySQL
SQL Server
PostgreSQL
SQLite
總結(jié)
在前面的文章中我們介紹了如何通過 SQL 聚合函數(shù)（LISTAGG、STRING_AGG、GROUP_CONCAT）將多行字符串合并成單個字符串。今天我們來討論一下字符串聚合的反操作，也就是將單個字符串拆分成多行字符串。本文涉及的數(shù)據(jù)庫包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。
表值函數(shù)
表值函數(shù)（Table-Valued Function）是指返回結(jié)果是一個表或者集合的函數(shù)，也稱為行集函數(shù)（Set Returning Function）。表值函數(shù)可以當(dāng)作一個數(shù)據(jù)表在查詢中使用，類似于子查詢或者視圖。在文章中我們會使用到以下示例表：
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 沒有提供拆分字符串的表值函數(shù)，我們可以創(chuàng)建一個自定義的 PL/SQL 函數(shù)來實現(xiàn)這個功能。首先，創(chuàng)建一個集合類型：
CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(4000);
1
str_list 可以看做一個由字符串?dāng)?shù)據(jù)組成的數(shù)組或者列表。然后創(chuàng)建一個拆分字符串的函數(shù)：
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 函數(shù)可以將輸入的字符串以指定分隔符進(jìn)行拆分，默認(rèn)分隔符為逗號。例如：
SELECT v.column_value
FROM string_split('Oracle,MySQL,SQL Server,PostgreSQL,SQLit') v;
COLUMN_VALUE|
------------|
Oracle |
MySQL |
SQL Server |
PostgreSQL |
SQLit |
我們也可以將該函數(shù)應(yīng)用到查詢中的字段，例如：
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 字段中的數(shù)據(jù)進(jìn)行了展開。
想一想，怎么查找劇情類的電影？
MySQL
MySQL 沒有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來實現(xiàn)這個功能。不過，我們可以利用遞歸通用表表達(dá)式來實現(xiàn)字符串的拆分：
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 表示遞歸通用表表達(dá)式，每次遞歸都返回一個拆分后的子串。將上面的查詢應(yīng)用到 movies 表中可以將電影的類型進(jìn)行展開：
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|唐伯虎點秋香|愛情 |
其他數(shù)據(jù)庫也都實現(xiàn)了通用表表達(dá)式，因此也可以使用這種方法進(jìn)行字符串的拆分。
SQL Server
SQL Server 2016 引入了一個字符串表值函數(shù) STRING_SPLIT，它可以根據(jù)指定的分隔符將字符串拆分為子字符串行。例如：
SELECT v.value
FROM string_split('Oracle,MySQL,SQL Server,PostgreSQL,SQLit', ',') v;
value |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLit |
STRING_SPLIT 函數(shù)第一個參數(shù)是被拆分的字符串，第二個參數(shù)是拆分使用的分隔符。函數(shù)返回一個單字段的表，字段名為“value” 。如果任何輸入?yún)?shù)為 nvarchar 或 nchar 類型，則返回 nvarchar 類型；否則，返回 varchar 類型。 返回類型的長度與字符串參數(shù)的長度相同。
以下查詢使用 CROSS APPLY 將 class 字段進(jìn)行了展開：
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 函數(shù)：
SELECT id, name, class
FROM movies
WHERE EXISTS (SELECT 1 FROM string_split(class, '、') WHERE value = '劇情');
id|name |class |
--|-------|---------------|
1|千與千尋|動畫、劇情、奇幻|
2|阿甘正傳|劇情、愛情 |
PostgreSQL
首先，PostgreSQL 中所有的函數(shù)實際上都可以作為表值函數(shù)使用。例如：
SELECT * FROM abs(10);
abs|
---|
10|
我們知道，F(xiàn)ROM 子句后面就是表，因此 ABS 函數(shù)的返回結(jié)果可以看做一個一行一列的表。
PostgreSQL 提供了一個拆分字符串的函數(shù) regexp_split_to_table ，可以通過一個 POSIX 正則表達(dá)式指定分隔符。例如：
SELECT *
FROM regexp_split_to_table('Oracle,MySQL,SQL Server,PostgreSQL,SQLit', ',') v;
v |
----------|
Oracle |
MySQL |
SQL Server|
PostgreSQL|
SQLit |
以下查詢使用 CROSS JOIN 將 class 字段進(jìn)行了展開：
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 沒有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來實現(xiàn)這個功能。不過，我們可以像 MySQL 一樣利用遞歸通用表表達(dá)式來實現(xiàn)字符串的拆分：
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 表示遞歸通用表表達(dá)式，每次遞歸都返回一個拆分后的子串。將上面的查詢應(yīng)用到 movies 表中可以將電影的類型進(jìn)行展開：
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|唐伯虎點秋香|愛情 |
總結(jié)
本文介紹了如何利用表值函數(shù)將單個字符串拆分為多行字符串，其中 SQL Server 和 PostgreSQL 提供了內(nèi)置的函數(shù)，Oracle 可以創(chuàng)建自定義 PL/SQL 函數(shù)，MySQL 和 SQLite 可以利用通用表表達(dá)式實現(xiàn)。
分類導(dǎo)航