SQL 表值函式之字串拆分

大家好，我是只談技術不剪發的 Tony 老師，

在前面的文章中我們介紹了如何通過 SQL 聚合函式（LISTAGG、STRING_AGG、GROUP_CONCAT）將多行字串合并成單個字串，今天我們來討論一下字串聚合的反操作，也就是將單個字串拆分成多行字串，本文涉及的資料庫包括 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));

INSERT INTO movies VALUES (1, '千與千尋', '影片、劇情、奇幻');
INSERT INTO movies VALUES (2, '阿甘正傳', '劇情、愛情');
INSERT INTO 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;
   END IF;
  END LOOP;
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  
CROSS JOIN 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  
CROSS APPLY 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  
WHERE EXISTS (SELECT 1 FROM string_split(class, '、') WHERE value = '劇情');

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
CROSS JOIN 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 Server 和 PostgreSQL 提供了內置的函式，Oracle 可以創建自定義 PL/SQL 函式，MySQL 和 SQLite 可以利用通用表運算式實作，