STRING_SPLIT (Transact-SQL)

適用於:Microsoft Fabric 中 Microsoft Fabric倉儲中的 SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse AnalyticsSQL 分析端點

STRING_SPLIT 是數據表值函式,會根據指定的分隔符,將字串分割成子字串的數據列。

相容性層級 130

STRING_SPLIT 需要相容性層級至少為130。 當層級小於 130 時,資料庫引擎 找不到函STRING_SPLIT式。

若要變更資料庫的相容性層級,請參閱 檢視或變更資料庫的相容性層級

注意

Azure Synapse Analytics 中不需要 STRING_SPLIT 相容性設定。

Transact-SQL 語法慣例

語法

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

引數

string

任何字元類型的表達式(例如 nvarcharvarchar、ncharchar)。

separator

任何字元類型的單一字元表達式(例如 nvarchar(1)、varchar(1)nchar(1)char(1),做為串連子字串的分隔符。

enable_ordinal

適用於:Azure SQL 資料庫、Azure SQL 受控執行個體、Azure Synapse Analytics(僅限無伺服器 SQL 集區),以及 SQL Server 2022 (16.x) 和更新版本

intbit運算式,做為旗標來啟用或停用 ordinal 輸出資料行。 的值 1 會啟用數據 ordinal 行。 如果 省略enable_ordinalNULL或 具有的值 0,則會停用數據 ordinal 行。

傳回類型

ordinal如果未啟用輸出數據行,STRING_SPLIT則會傳回單一數據行數據表,其數據列是子字串。 資料行的名稱為 value。 如果任何輸入引數是 nvarcharnchar,其會傳回 nvarchar。 否則,傳回 varchar。 傳回類型的長度與 string 引數的長度相同。

如果傳遞 enable_ordinal 自變數的值1,則會傳回名為 ordinal 的第二個數據行,其中包含輸入字串中每個子字串位置的 1 起始索引值。 傳回類型為 bigint

備註

STRING_SPLIT 輸入具有分隔子字串和輸入一個字元做為分隔符或分隔符的字串。 選擇性地,函式支援第三個自變數,其值為 01 ,可分別 ordinal 停用或啟用輸出數據行。

STRING_SPLIT視enable_ordinal自變數而定,輸出單一數據行或雙欄數據表。

  • 如果 enable_ordinalNULL、省略或具有的值 0STRING_SPLIT 則會傳回包含子字串的單一數據行數據表。 輸出資料行的名稱為 value

  • 如果 enable_ordinal 具有的值 1,則函式會傳回兩個數據行數據表,包括 ordinal 由原始輸入字串中子字串的 1 起始索引值所組成的數據行。

enable_ordinal自變數必須是常數值,而不是數據行或變數。 它也必須是具有 或 1值的0int 數據類型。 否則,函式會引發錯誤。

輸出資料列可能為任何順序。 順序不保證符合輸入字串中的子字串順序。 您可以使用 語句上的 SELECT 子句來覆寫最終排序順序ORDER BY,例如 ORDER BY valueORDER BY ordinal

0x0000char(0)) 是 Windows 定序中未定義的字元,不能包含在 中 STRING_SPLIT

當輸入字串包含兩個或更多個連續出現的分隔符號字元時,會出現長度為零的空白子字串。 空白子字串視為純文字子字串來處理。 您可以使用 子句來篩選出包含空子字串 WHERE 的任何資料列,例如 WHERE value <> ''。 如果輸入字串為 NULL,則 STRING_SPLIT 數據表值函式會傳回空的數據表。

例如,下列 SELECT 語句會使用空格字元做為分隔符:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

在練習中,上述 SELECT 傳回的結果數據表如下:

value
Lorem
ipsum
dolor
sit
amet.

下列範例會藉由傳遞1選擇性的第三個自變數來啟用資料ordinal行:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

此陳述式接著會傳回下列結果資料表:

value 序數
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

範例

A. 分割逗號分隔值字串

剖析值的逗號分隔清單,並傳回所有非空白的權杖:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

STRING_SPLIT 如果分隔符之間沒有任何專案,則傳回空字串。 條件 RTRIM(value) <> '' 會移除空的令牌。

B. 分割資料行中的逗號分隔值字串

Product 資料表有一個資料行含有標籤的逗號分隔清單,如下列範例所示:

ProductId 名稱 Tags (標籤)
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

下列查詢會轉換每個標記清單,並將它們與原始資料列結合:

SELECT ProductId, Name, value
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',');

以下為結果集。

ProductId 名稱
1 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

注意

輸出的順序可能會隨著順序 保證符合輸入字串中子字串的順序而有所不同。

C. 依據值彙總

使用者必須建立顯示每個標籤之產品數的報告,依據產品數排序,並僅篩選超過兩個產品的標籤。

SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;

D. 依據標籤值來搜尋

開發人員必須建立依據關鍵字尋找發行項的查詢。 他們可以使用下列查詢:

若要尋找有單一標籤 (clothing) 的產品:

SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

尋找有兩個指定標籤 (clothing 和 road) 的產品:

SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
    FROM STRING_SPLIT(Tags, ',')
    WHERE value IN ('clothing', 'road'));

E. 依據值清單來尋找資料列

開發人員必須建立依據識別碼清單尋找發行項的查詢。 他們可以使用下列查詢:

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

上述 STRING_SPLIT 用法是一般反模式的取代專案。 這類反模式可以涉及在應用層或 Transact-SQL 中建立動態 SQL 字串。 或者,您可以使用 LIKE 運算符來達成反模式。 請參閱下列範例 SELECT 語句:

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. 依序數值尋找資料列

下列陳述式會尋找具有偶數索引值的所有資料列:

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

上述陳述式會傳回下列資料表:

value 序數
Texas 2
Washington 4
Colorado 6

G. 依序數值排序資料列

下列陳述式會傳回輸入字串的分割子字串值及其序數值,依 ordinal 資料行排序:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

上述陳述式會傳回下列資料表:

value 序數
A 5
B 4
C 3
D 2
E 1