STRING_SPLIT (Transact-SQL)STRING_SPLIT (Transact-SQL)

适用于: SQL Server 2016 及更高版本 Azure SQL 数据库 Azure Synapse Analytics (SQL DW) Parallel 数据仓库

一个表值函数,它根据指定的分隔符将字符串拆分为子字符串行。A table-valued function that splits a string into rows of substrings, based on a specified separator character.

兼容性级别为 130Compatibility level 130

STRING_SPLIT 要求兼容性级别至少为 130。STRING_SPLIT requires the compatibility level to be at least 130. 级别低于 130 时,SQL Server 找不到 STRING_SPLIT 函数。When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

若要更改数据库的兼容性级别,请参阅查看或更改数据库的兼容性级别To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

STRING_SPLIT ( string , separator )  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

stringstring
任何字符类型(例如 nvarchar、varchar、nchar 或 char)的表达式Is an expression of any character type (for example, nvarchar, varchar, nchar, or char).

separator separator
任何字符类型(例如 nvarchar(1)、varchar(1)、nchar(1) 或 char(1))的单字符表达式,用作串联子字符串的分隔符 。Is a single character expression of any character type (for example, nvarchar(1), varchar(1), nchar(1), or char(1)) that is used as separator for concatenated substrings.

返回类型Return Types

返回其行是子字符串的单列表。Returns a single-column table whose rows are the substrings. 该列名为“value” 。The name of the column is value. 如果任何输入参数为 nvarchar 或 nchar,则返回 nvarchar 。Returns nvarchar if any of the input arguments are either nvarchar or nchar. 否则,返回 varchar 。Otherwise returns varchar. 返回类型的长度与字符串参数的长度相同。The length of the return type is the same as the length of the string argument.

备注Remarks

STRING_SPLIT 输入包含分隔子字符串的字符串,并输入一个字符用作分隔符 。STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT 输出其行包含子字符串的单列表。STRING_SPLIT outputs a single-column table whose rows contain the substrings. 输出列的名称为“value” 。The name of the output column is value.

输出行可以按任意顺序排列。The output rows might be in any order. 顺序不保证与输入字符串中的子字符串顺序匹配 。The order is not guaranteed to match the order of the substrings in the input string. 可以通过在 SELECT 语句中使用 ORDER BY 子句覆盖最终排序顺序 (ORDER BY value)。You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

0x0000 (char(0)) 是 Windows 排序规则中未定义的字符,不能包括在 STRING_SPLIT 中 。0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in STRING_SPLIT.

当输入字符串包含两个或多个连续出现的分隔符字符时,将出现长度为零的空子字符串。Empty zero-length substrings are present when the input string contains two or more consecutive occurrences of the delimiter character. 空子字符串的处理方式与普通子字符串相同。Empty substrings are treated the same as are plain substrings. 可以通过使用 WHERE 子句筛选出包含空的子字符串的任何行 (WHERE value <> '')。You can filter out any rows that contain the empty substring by using the WHERE clause (WHERE value <> ''). 如果输入字符串为 NULL,则 STRING_SPLIT 表值函数返回一个空表。If the input string is NULL, the STRING_SPLIT table-valued function returns an empty table.

例如,以下 SELECT 语句使用空格字符作为分隔符:As an example, the following SELECT statement uses the space character as the separator:

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

在实践运行中,前面的 SELECT 返回以下结果表:In a practice run, the preceding SELECT returned following result table:

value
LoremLorem
ipsumipsum
dolordolor
sitsit
amet.amet.
 

示例Examples

A.A. 拆分逗号分隔值字符串Split comma-separated value string

分析逗号分隔值列表,并返回所有非空标记:Parse a comma-separated list of values and return all non-empty tokens:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

如果分隔符之间没有任何内容,STRING_SPLIT 将返回空字符串。STRING_SPLIT will return empty string if there is nothing between separator. 条件 RTRIM(value) <> '' 将删除空标记。Condition RTRIM(value) <> '' will remove empty tokens.

B.B. 拆分一列中的逗号分隔值字符串Split comma-separated value string in a column

生产表中的某一列为逗号分隔的标记列表,如以下示例所示:Product table has a column with comma-separate list of tags shown in the following example:

ProductIdProductId 名称Name TagsTags
11 Full-Finger GlovesFull-Finger Gloves clothing,road,touring,bikeclothing,road,touring,bike
22 LL HeadsetLL Headset bikebike
33 HL Mountain FrameHL Mountain Frame bike,mountainbike,mountain

下面的查询转换每个标记列表,并将它们与原始行联接起来:Following query transforms each list of tags and joins them with the original row:

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

下面是结果集:Here is the result set.

ProductIdProductId 名称Name value
11 Full-Finger GlovesFull-Finger Gloves clothingclothing
11 Full-Finger GlovesFull-Finger Gloves 道路road
11 Full-Finger GlovesFull-Finger Gloves touringtouring
11 Full-Finger GlovesFull-Finger Gloves bikebike
22 LL HeadsetLL Headset bikebike
33 HL Mountain FrameHL Mountain Frame bikebike
33 HL Mountain FrameHL Mountain Frame mountainmountain

备注

输出的顺序不保证与输入字符串中的子字符串顺序匹配 。The order of the output may vary as the order is not guaranteed to match the order of the substrings in the input string.

C.C. 按值聚合Aggregation by values

用户必须创建一个报表,表中显示每个标记的产品数量并按产品数量排序,然后只筛选出产品数量在两个以上的标记。Users must create a report that shows the number of products per each tag, ordered by number of products, and to filter only the tags with more than two products.

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.D. 按标记值搜索Search by tag value

开发人员必须创建按关键字查找文章的查询。Developers must create queries that find articles by keywords. 可以使用以下查询:They can use following queries:

查找具有单个标记 (clothing) 的产品:To find products with a single tag (clothing):

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

查找具有两个指定标记(clothing 和 road)的产品:Find products with two specified tags (clothing and road):

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

E.E. 按一系列值查找行Find rows by list of values

开发人员必须创建一个按 ID 列表查找文章的查询。Developers must create a query that finds articles by a list of IDs. 可以使用以下查询:They can use following query:

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

上述 STRING_SPLIT 使用情况是常见反模式的替代。The preceding STRING_SPLIT usage is a replacement for a common anti-pattern. 此类反模式可能涉及在应用程序层或 Transact-SQL 中创建动态 SQL 字符串。Such an anti-pattern can involve the creation of a dynamic SQL string in the application layer or in Transact-SQL. 或者可以通过使用 LIKE 运算符来实现反模式。Or an anti-pattern can be achieved by using the LIKE operator. 请参阅以下示例 SELECT 语句:See the following example SELECT statement:

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

另请参阅See Also