SQL表值函數(shù)之字符串拆分

作者：不剪發(fā)的Tony老師 2025-03-03 10:51:29

今天我們來討論一下字符串聚合的反操作，也就是將單個字符串拆分成多行字符串。本文涉及的數(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));

INSERTINTO movies VALUES(1,'千與千尋','動畫、劇情、奇幻');
INSERTINTO movies VALUES(2,'阿甘正傳','劇情、愛情');
INSERTINTO 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);

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

string_split 函數(shù)可以將輸入的字符串以指定分隔符進行拆分，默認(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  
CROSSJOIN string_split(class,'、');

ID|NAME        |COLUMN_VALUE|
--|------------|------------|
1|千與千尋    |動畫         |
1|千與千尋    |劇情         |
1|千與千尋    |奇幻         |
2|阿甘正傳    |劇情         |
2|阿甘正傳    |愛情         |
3|唐伯虎點秋香|喜劇         |
3|唐伯虎點秋香|古裝         |
3|唐伯虎點秋香|愛情         |

查詢通過交叉連接將 class 字段中的數(shù)據(jù)進行了展開。

想一想，怎么查找劇情類的電影？

MySQL

MySQL 沒有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來實現(xiàn)這個功能。不過，我們可以利用遞歸通用表表達式來實現(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 表示遞歸通用表表達式，每次遞歸都返回一個拆分后的子串。將上面的查詢應(yīng)用到 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|唐伯虎點秋香|愛情 |

其他數(shù)據(jù)庫也都實現(xià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 字段進行了展開：

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 函數(shù)：

SELECT id, name, class  
FROM movies  
WHEREEXISTS(SELECT1FROM string_split(class,'、')WHEREvalue='劇情');

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 正則表達式指定分隔符。例如：

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 沒有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來實現(xiàn)這個功能。不過，我們可以像 MySQL 一樣利用遞歸通用表表達式來實現(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    |