SQL Server 2022 STRING_SPLIT表值函数特性增强

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                                        3
                                                 4
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 Product
    CROSS 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
  • 63
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值