STRING_SPLIT (Transact-SQL)

适用于:yesSQL Server 2016 (13.x) 和更高版本YesAzure SQL 数据库YesAzure SQL 托管实例yesAzure Synapse Analytics

一个表值函数,它根据指定的分隔符将字符串拆分为子字符串行。

兼容性级别为 130

STRING_SPLIT 要求兼容性级别至少为 130。 级别低于 130 时,SQL Server 找不到 STRING_SPLIT 函数。

若要更改数据库的兼容性级别,请参阅查看或更改数据库的兼容性级别

注意

在 Azure Synapse Analytics 中,无需对 STRING_SPLIT 进行兼容性配置。

Topic link iconTransact-SQL 语法约定

语法

STRING_SPLIT ( string , separator [ , enable_ordinal ] )  

参数

string

任何字符类型(例如 nvarchar、varchar、nchar 或 char)的表达式

separator

任何字符类型(例如 nvarchar(1)、varchar(1)、nchar(1) 或 char(1))的单字符表达式,用作串联子字符串的分隔符 。

enable_ordinal

一个 int 或 bit 表达式,用作启用或禁用 ordinal 输出列的标志。 如果值为 1,则启用 ordinal 列。 如果省略 enable_ordinal、为 NULL 或值为 0,则禁用 ordinal 列。

注意

目前,enable_ordinal 参数和 ordinal 输出列在 Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics(仅限无服务器 SQL 池)中受支持。 从 SQL Server 2022 (16.x) 预览版起,此参数和输出列将在 SQL Server 中可用。

返回类型

如果未启用 ordinal 输出列,STRING_SPLIT 将返回一个单列表,其中的行为子字符串。 列的名称为 value。 如果任何输入参数为 nvarchar 或 nchar,则它返回 nvarchar 。 否则,将返回 varchar。 返回类型的长度与字符串参数的长度相同。

如果 enable_ordinal 参数传递的值为1,则返回第二个名为 ordinal 的列,其中包含每个子字符串在输入字符串中的位置(从 1 开始的索引值)。 返回类型为 bigint。

注解

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 数据类型。 否则,此函数将引发错误。

输出行可以按任意顺序排列。 顺序不保证与输入字符串中的子字符串顺序匹配 。 可以通过在 SELECT 语句中使用 ORDER BY 子句覆盖最终排序顺序,例如,ORDER BY valueORDER BY ordinal

0x0000 (char(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. 拆分一列中的逗号分隔值字符串

生产表中的某一列为逗号分隔的标记列表,如以下示例所示:

ProductId 名称 标记
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 名称 value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves 道路
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. 按一系列值查找行

开发人员必须创建一个按 ID 列表查找文章的查询。 可以使用以下查询:

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

后续步骤