在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

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

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        表值函數(shù)
        Oracle
        MySQL
        SQL Server
        PostgreSQL
        SQLite
        總結(jié)



在前面的文章中我們介紹了如何通過(guò) SQL 聚合函數(shù)（LISTAGG、STRING_AGG、GROUP_CONCAT）將多行字符串合并成單個(gè)字符串。今天我們來(lái)討論一下字符串聚合的反操作，也就是將單個(gè)字符串拆分成多行字符串。本文涉及的數(shù)據(jù)庫(kù)包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。


表值函數(shù)

表值函數(shù)（Table-Valued Function）是指返回結(jié)果是一個(gè)表或者集合的函數(shù)，也稱(chēng)為行集函數(shù)（Set Returning Function）。表值函數(shù)可以當(dāng)作一個(gè)數(shù)據(jù)表在查詢(xún)中使用，類(lèi)似于子查詢(xún)或者視圖。在文章中我們會(huì)使用到以下示例表：

CREATE TABLE movies(id int primary key, name varchar(50), class varchar(200));

INSERT INTO movies VALUES (1, '千與千尋', '動(dòng)畫(huà)、劇情、奇幻');
INSERT INTO movies VALUES (2, '阿甘正傳', '劇情、愛(ài)情');
INSERT INTO movies VALUES (3, '唐伯虎點(diǎn)秋香', '喜劇、古裝、愛(ài)情');

    

Oracle

Oracle 沒(méi)有提供拆分字符串的表值函數(shù)，我們可以創(chuàng)建一個(gè)自定義的 PL/SQL 函數(shù)來(lái)實(shí)現(xiàn)這個(gè)功能。首先，創(chuàng)建一個(gè)集合類(lèi)型：

CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(4000);

    1

str_list 可以看做一個(gè)由字符串?dāng)?shù)據(jù)組成的數(shù)組或者列表。然后創(chuàng)建一個(gè)拆分字符串的函數(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)分隔符為逗號(hào)。例如：

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)用到查詢(xún)中的字段，例如：

SELECT id, name, column_value
FROM movies  
CROSS JOIN string_split(class, '、');

ID|NAME        |COLUMN_VALUE|
--|------------|------------|
 1|千與千尋    |動(dòng)畫(huà)         |
 1|千與千尋    |劇情         |
 1|千與千尋    |奇幻         |
 2|阿甘正傳    |劇情         |
 2|阿甘正傳    |愛(ài)情         |
 3|唐伯虎點(diǎn)秋香|喜劇         |
 3|唐伯虎點(diǎn)秋香|古裝         |
 3|唐伯虎點(diǎn)秋香|愛(ài)情         |


查詢(xún)通過(guò)交叉連接將 class 字段中的數(shù)據(jù)進(jìn)行了展開(kāi)。

想一想，怎么查找劇情類(lèi)的電影？
MySQL

MySQL 沒(méi)有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來(lái)實(shí)現(xiàn)這個(gè)功能。不過(guò)，我們可以利用遞歸通用表表達(dá)式來(lái)實(shí)現(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á)式，每次遞歸都返回一個(gè)拆分后的子串。將上面的查詢(xún)應(yīng)用到 movies 表中可以將電影的類(lèi)型進(jìn)行展開(kāi)：

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|千與千尋    |動(dòng)畫(huà) |
 2|阿甘正傳    |劇情 |
 3|唐伯虎點(diǎn)秋香|喜劇 |
 1|千與千尋    |劇情 |
 2|阿甘正傳    |愛(ài)情 |
 3|唐伯虎點(diǎn)秋香|古裝 |
 1|千與千尋    |奇幻 |
 3|唐伯虎點(diǎn)秋香|愛(ài)情 |

   

其他數(shù)據(jù)庫(kù)也都實(shí)現(xiàn)了通用表表達(dá)式，因此也可以使用這種方法進(jìn)行字符串的拆分。
SQL Server

SQL Server 2016 引入了一個(gè)字符串表值函數(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ù)第一個(gè)參數(shù)是被拆分的字符串，第二個(gè)參數(shù)是拆分使用的分隔符。函數(shù)返回一個(gè)單字段的表，字段名為“value” 。如果任何輸入?yún)?shù)為 nvarchar 或 nchar 類(lèi)型，則返回 nvarchar 類(lèi)型；否則，返回 varchar 類(lèi)型。 返回類(lèi)型的長(zhǎng)度與字符串參數(shù)的長(zhǎng)度相同。

以下查詢(xún)使用 CROSS APPLY 將 class 字段進(jìn)行了展開(kāi)：

SELECT id, name, value  
FROM movies  
CROSS APPLY string_split(class, '、');

id|name        |value |
--|------------|------|
 1|千與千尋    |動(dòng)畫(huà)   |
 1|千與千尋    |劇情   |
 1|千與千尋    |奇幻   |
 2|阿甘正傳    |劇情   |
 2|阿甘正傳    |愛(ài)情   |
 3|唐伯虎點(diǎn)秋香|喜劇   |
 3|唐伯虎點(diǎn)秋香|古裝   |
 3|唐伯虎點(diǎn)秋香|愛(ài)情   |

   

SQL Server 不能像 Oracle 那樣直接使用連接查詢(xún)。

如果想要查找劇情類(lèi)的電影，可以在子查詢(xún)中使用 string_split 函數(shù)：

SELECT id, name, class  
FROM movies  
WHERE EXISTS (SELECT 1 FROM string_split(class, '、') WHERE value = '劇情');

id|name   |class          |
--|-------|---------------|
 1|千與千尋|動(dòng)畫(huà)、劇情、奇幻|
 2|阿甘正傳|劇情、愛(ài)情     |

    

PostgreSQL

首先，PostgreSQL 中所有的函數(shù)實(shí)際上都可以作為表值函數(shù)使用。例如：

SELECT * FROM abs(10);

abs|
---|
 10|

 

我們知道，F(xiàn)ROM 子句后面就是表，因此 ABS 函數(shù)的返回結(jié)果可以看做一個(gè)一行一列的表。

PostgreSQL 提供了一個(gè)拆分字符串的函數(shù) regexp_split_to_table ，可以通過(guò)一個(gè) POSIX 正則表達(dá)式指定分隔符。例如：

SELECT *
FROM regexp_split_to_table('Oracle,MySQL,SQL Server,PostgreSQL,SQLit', ',') v;

v         |
----------|
Oracle    |
MySQL     |
SQL Server|
PostgreSQL|
SQLit     |

    

以下查詢(xún)使用 CROSS JOIN 將 class 字段進(jìn)行了展開(kāi)：

SELECT *
FROM movies
CROSS JOIN regexp_split_to_table(class, '、') v;

id|name        |class         |v  |
--|------------|--------------|---|
 1|千與千尋    |動(dòng)畫(huà)、劇情、奇幻|動(dòng)畫(huà)|
 1|千與千尋    |動(dòng)畫(huà)、劇情、奇幻|劇情|
 1|千與千尋    |動(dòng)畫(huà)、劇情、奇幻|奇幻|
 2|阿甘正傳    |劇情、愛(ài)情     |劇情|
 2|阿甘正傳    |劇情、愛(ài)情     |愛(ài)情|
 3|唐伯虎點(diǎn)秋香|喜劇、古裝、愛(ài)情|喜劇|
 3|唐伯虎點(diǎn)秋香|喜劇、古裝、愛(ài)情|古裝|
 3|唐伯虎點(diǎn)秋香|喜劇、古裝、愛(ài)情|愛(ài)情|

    

想一想，怎么查找劇情類(lèi)的電影？
SQLite

SQLite 沒(méi)有提供拆分字符串的表值函數(shù)，也不支持自定義函數(shù)來(lái)實(shí)現(xiàn)這個(gè)功能。不過(guò)，我們可以像 MySQL 一樣利用遞歸通用表表達(dá)式來(lái)實(shí)現(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á)式，每次遞歸都返回一個(gè)拆分后的子串。將上面的查詢(xún)應(yīng)用到 movies 表中可以將電影的類(lèi)型進(jìn)行展開(kāi)：

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|千與千尋    |動(dòng)畫(huà) |
 2|阿甘正傳    |劇情 |
 3|唐伯虎點(diǎn)秋香|喜劇 |
 1|千與千尋    |劇情 |
 2|阿甘正傳    |愛(ài)情 |
 3|唐伯虎點(diǎn)秋香|古裝 |
 1|千與千尋    |奇幻 |
 3|唐伯虎點(diǎn)秋香|愛(ài)情 |



總結(jié)

本文介紹了如何利用表值函數(shù)將單個(gè)字符串拆分為多行字符串，其中 SQL Server 和 PostgreSQL 提供了內(nèi)置的函數(shù)，Oracle 可以創(chuàng)建自定義 PL/SQL 函數(shù)，MySQL 和 SQLite 可以利用通用表表達(dá)式實(shí)現(xiàn)。