SQL Server 2022 STRING_SPLIT表值函數特性增強
1、本文內容
- List item
- 語法
- 參數
- 返回類型
- 注解
適用于：SQL Server 2016 (13.x) 及更高版本Azure SQL 數據庫Azure SQL 托管實例Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 分析終結點Microsoft Fabric 中的倉庫
STRING_SPLIT 是一個表值函數，它根據指定的分隔符將字符串拆分為子字符串行。
兼容性級別為 130
STRING_SPLIT 要求兼容性級別至少為 130。 該級別低于 130 時，數據庫引擎將找不到 STRING_SPLIT 函數。
若要更改數據庫的兼容性級別，請參閱查看或更改數據庫的兼容性級別。
備注：在 Azure Synapse Analytics 中，無需對 STRING_SPLIT 進行兼容性配置。
參考官方文檔地址
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16
2、語法
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
3、參數
string
任何字符類型（例如 nvarchar、varchar、nchar 或 char）的表達式。
separator
任何字符類型（例如nvarchar(1)、varchar(1)、nchar(1) 或 char(1)）的單字符表達式，用作串聯子字符串的分隔符。
enable_ordinal
適用于：Azure SQL 數據庫、Azure SQL 托管實例、Azure Synapse Analytics（僅限無服務器 SQL 池）、SQL Server 2022 (16.x) 及更高版本，也是該版本新加特性。
一個 int 或 bit 表達式，用作啟用或禁用 ordinal 輸出列的標志。 如果值為 1，則啟用 ordinal 列。 如果省略 enable_ordinal 、為 NULL 或值為 0，則禁用 ordinal 列。
4、返回類型
如果未啟用 ordinal 輸出列，STRING_SPLIT 將返回一個單列表，其中的行為子字符串。 列的名稱為 value。 如果任何輸入參數為 nvarchar 或 nchar，則它返回 nvarchar 。 否則，將返回 varchar。 返回類型的長度與字符串參數的長度相同。
如果 enable_ordinal 參數傳遞的值為 1，則返回第二個名為 ordinal 的列，其中包含每個子字符串在輸入字符串中的位置（從 1 開始的索引值）。 返回類型為 bigint
5、注解
STRING_SPLIT 輸入一個包含分隔子字符串的字符串，并輸入一個字符用作分隔符。 根據需要，函數還支持值為 0 或 1 的第三個參數，該參數分別禁用或啟用了 ordinal 輸出列。
STRING_SPLIT 輸出一個單列表或雙列表，具體取決于 enable_ordinal 參數。
如果 enable_ordinal 為 NULL、被省略或值為 0，STRING_SPLIT 將返回一個單列表，其中的行包含子字符串。 輸出列的名稱為 value。
如果 enable_ordinal 的值為 1，該函數將返回一個包含兩列的表，其中 ordinal 列由原始輸入字符串中從 1 開始的子字符串的索引值組成。
請注意，enable_ordinal 參數必須是常數值，而不能是列或變量。 它還必須是值為 0 或 1 的 bit 或 int 數據類型。 否則，此函數將引發錯誤。
輸出行可以按任意順序排列。 順序不保證與輸入字符串中的子字符串順序匹配。 可以通過使用 ORDER BY 子句（在 SELECT 語句中）覆蓋最終排序順序，例如 ORDER BY value 或 ORDER BY ordinal。
0x0000 (char(0)) 是 Windows 排序規則中未定義的字符，無法包括在 STRING_SPLIT 中。
當輸入字符串包含兩個或多個連續出現的分隔符字符時，將出現長度為零的空子字符串。 空子字符串的處理方式與普通子字符串相同。 可以通過使用 WHERE 子句篩選出包含空的子字符串的任何行，例如 WHERE value <> ‘’。 如果輸入字符串為 NULL，則 STRING_SPLIT 表值函數返回一個空表。
5.1、在SQLServer2016/2019版本，執行語句
SELECT * FROM STRING_SPLIT('hello sqlserver2022 SQLServer Management Studio',' ')value ordinal
----------------------------------------------- --------------------
hello 1
sqlserver2022 2
SQLServer 3
Management 4
Studio 5
-- 如果多指定一個參數，表示啟用enable_ordinal ，SQLServer2016/2019版本就不支持
SELECT * FROM STRING_SPLIT('hello sqlserver2022 SQLServer Management Studio',' ',1)
/*消息 8144，級別 16，狀態 3，第 100 行
為過程或函數 STRING_SPLIT 指定了過多的參數*/
5.2、在SQLServer2022
SELECT * FROM STRING_SPLIT('hello sqlserver2022 SQLServer Management Studio', ' ',1);value ordinal
----------------------------------------------- --------------------
hello 1
sqlserver2022 2
SQLServer 3
Management 4
Studio 5
6、示例
6.1、拆分逗號分隔值字符串
分析逗號分隔值列表，并返回所有非空標記：
SELECT * FROM STRING_SPLIT('hello,sqlserver2022,SQLServer,,Management Studio', ',',1);value ordinal
------------------------------------------------ --------------------
hello 1
sqlserver2022 2
SQLServer 34
Management Studio 5-- RTRIM(value) 過濾空值
SELECT * FROM STRING_SPLIT('hello,sqlserver2022,SQLServer,,Management Studio', ',',1) WHERE RTRIM(value) <> '';value ordinal
------------------------------------------------ --------------------
hello 1
sqlserver2022 2
SQLServer 3
Management Studio 5
-- 如果分隔符之間沒有任何內容，STRING_SPLIT將返回空字符串。RTRIM(value) <> '' 條件將過濾空值value。6.2、 拆分一列中的逗號分隔值字符串
生產表中的某一列為逗號分隔的標記列表，如以下示例所示：
WITH Product(ProductId,NameInfo,tags) AS (
SELECT 1,'mssql','sqlserver2016,sqlserver2019,sqlserver2022'
UNION ALL
SELECT 2,'oracle','oracle11g,oracle12c,mysql8.4'
)
SELECT ProductId, NameInfo, value
FROM ProductCROSS APPLY STRING_SPLIT(Tags, ',');ProductId NameInfo value
----------- -------- -----------------------------------------
1 mssql sqlserver2016
1 mssql sqlserver2019
1 mssql sqlserver2022
2 oracle oracle11g
2 oracle oracle12c
2 oracle mysql8.4
6.3、按序號值為行排序
下面的語句返回輸入字符串的拆分子字符串值及其序號值，按 ordinal 列排序
-- 倒序排序
SELECT * FROM STRING_SPLIT(N'深圳-廣州-中山-東莞-珠海', '-', 1) ORDER BY ordinal DESC;value ordinal
-------------- --------------------
珠海 5
東莞 4
中山 3
廣州 2
深圳 1
-- 升序排序
SELECT * FROM STRING_SPLIT(N'深圳-廣州-中山-東莞-珠海', '-', 1) ORDER BY ordinal;value ordinal
-------------- --------------------
深圳 1
廣州 2
中山 3
東莞 4
珠海 5
6.4、按序號值查找行
以下語句將查找具有偶數索引值的所有行
SELECT value AS v_split, ordinal FROM STRING_SPLIT(N'深圳-廣州-中山-東莞-珠海', '-', 1) WHERE ordinal % 2 = 0;v_split ordinal
-------------- --------------------
廣州 2
東莞 4
6.5、按標記值搜索
6.5.1、查找具有單個標記 (sqlserver2022) 的數據行
WITH Product(ProductId,NameInfo,tags) AS (
SELECT 1,'mssql','sqlserver2016,sqlserver2019,sqlserver2022'
UNION ALL
SELECT 2,'oracle','oracle11g,oracle12c,mysql8.4'
)
SELECT ProductId, NameInfo, Tags
FROM Product
WHERE 'sqlserver2022' IN (SELECT value FROM STRING_SPLIT(Tags, ','));
ProductId NameInfo Tags
----------- -------- -----------------------------------------
1 mssql sqlserver2016,sqlserver2019,sqlserver2022
6.5.2、查找具有兩個指定標記（sqlserver2022和 mysql8.4）的數據行
WITH Product(ProductId,NameInfo,tags) AS (
SELECT 1,'mssql','sqlserver2016,sqlserver2019,sqlserver2022'
UNION ALL
SELECT 2,'oracle','oracle11g,oracle12c,mysql8.4'
UNION ALL
SELECT 3,'postgresql','postgresql16,postgresql15'
)
SELECT ProductId, NameInfo, Tags
FROM Product
WHERE EXISTS (SELECT *FROM STRING_SPLIT(Tags, ',')WHERE value IN ('sqlserver2022', 'mysql8.4'));ProductId NameInfo Tags
----------- ---------- -----------------------------------------
1 mssql sqlserver2016,sqlserver2019,sqlserver2022
2 oracle oracle11g,oracle12c,mysql8.4
6.6、按一系列值查找行
WITH Product(ProductId,NameInfo,tags) AS (
SELECT 1,'mssql','sqlserver2016,sqlserver2019,sqlserver2022'
UNION ALL
SELECT 2,'oracle','oracle11g,oracle12c,mysql8.4'
UNION ALL
SELECT 3,'postgresql','postgresql16,postgresql15'
)
SELECT ProductId, NameInfo, Tags
FROM Product
JOIN STRING_SPLIT('1,3',',')ON value = ProductId;ProductId NameInfo Tags
----------- ---------- -----------------------------------------
1 mssql sqlserver2016,sqlserver2019,sqlserver2022
3 postgresql postgresql16,postgresql15
——A survey of uncertainty in deep neural networks)
