SQL Server中的STRING_SPLIT函数

This article will cover the STRING_SPLIT function in SQL Server including an overview and detailed usage examples.

本文将介绍SQL Server中的STRING_SPLIT函数，包括概述和详细的用法示例。

SQL Server users usually need string parsing and manipulation functions. In several scenarios, this string parsing or manipulation operation can be very painful for developers or database administrators. For this reason, in every SQL Server version, Microsoft has announced new string functions. New string functions like STRING_ESCAPE, STRING_SPLIT were added into SQL Server 2016 and CONCAT_WS, STRING_AGG, TRANSLATE, TRIM string functions were added into SQL Server 2017.

SQL Server用户通常需要字符串解析和操作功能。 在几种情况下，这种字符串解析或操作操作对于开发人员或数据库管理员而言可能非常痛苦。 因此，在每个SQL Server版本中，Microsoft都宣布了新的字符串函数。 SQL Server 2016中添加了新的字符串函数，例如STRING_ESCAPE，STRING_SPLIT，SQL Server 2017中添加了CONCAT_WS，STRING_AGG，TRANSLATE，TRIM字符串函数。

In this article, we will discuss the STRING_SPLIT function, in particular. The purpose of this built-in string function is to convert string arrays to columns which are separated by any separator. The below figure illustrates the main idea of this function.

在本文中，我们将特别讨论STRING_SPLIT函数。 此内置字符串函数的目的是将字符串数组转换为由任何分隔符分隔的列。 下图说明了此功能的主要思想。

As we already noted in the entry section of the article, this function was introduced in SQL Server 2016 and the previous versions of SQL Server do not support this built-in function. In other words, this function does not support under the 130 compatibility level. The following table illustrates the versions of SQL Server and their compatibility levels.

正如我们在文章的入门部分中已经指出的那样，此功能是在SQL Server 2016中引入的，而以前SQL Server版本不支持此内置功能。 换句话说，该功能在130兼容级别下不支持。 下表说明了SQL Server的版本及其兼容性级别。

SQL Server Versions	Compatibility Level
SQL Server 2019 preview	150
SQL Server 2017 (14.x)	140
SQL Server 2016 (13.x)	130
SQL Server 2014 (12.x)	120
SQL Server 2012 (11.x)	110
SQL Server 2008 R2	100
SQL Server 2008	100
SQL Server 2005 (9.x)	90
SQL Server 2000	80

SQL Server版本	相容性等级
SQL Server 2019预览版	150
SQL Server 2017（14.x）	140
SQL Server 2016（13.x）	130
SQL Server 2014（12.x）	120
SQL Server 2012（11.x）	110
SQL Server 2008 R2	100
SQL Server 2008	100
SQL Server 2005（9.x）	90
SQL Server 2000	80

Now, let’s start to discuss usage concepts and other details of this function.

现在，让我们开始讨论此功能的用法概念和其他细节。

句法： (Syntax: )

The syntax is very simple as this table valued built-in function takes only two parameters. First one is a string and the second one is a single character.

语法非常简单，因为此表值内置函数仅采用两个参数。 第一个是字符串，第二个是单个字符。

STRING_SPLIT（字符串，分隔符） (STRING_SPLIT (string, separator))

The following sample shows simplest usage of this function.

以下示例显示了此功能的最简单用法。

select value from STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',')

The following SELECT query will return an error because of the database compatibility level.

由于数据库兼容性级别，以下SELECT查询将返回错误。

ALTER DATABASE AdventureWorks2012 SET compatibility_LEVEL=120
GO
select value 
from 
STRING_SPLIT('apple , banana , lemon , kiwi , orange ,coconut',',') 

The reason for this error is that we decreased the database compatibility level under the 130 and SQL Server returns an error. Keep in mind, that if you are planning to use this function in your customer environment you have to be sure about their database compatibility level.

发生此错误的原因是，我们降低了130下的数据库兼容性级别，并且SQL Server返回了错误。 请记住，如果您打算在客户环境中使用此功能，则必须确保其数据库兼容性级别。

STRING_SPLIT和WHERE子句： (STRING_SPLIT and WHERE clause:)

Through the WHERE clause, we can filter the result set of the STRING_SPLIT function. In the following select statement, the WHERE clause will filter the result set of the function and will only return the row or rows which start with “le”

通过WHERE子句，我们可以过滤STRING_SPLIT函数的结果集。 在下面的select语句中，WHERE子句将过滤函数的结果集，并且仅返回以“ le”开头的一行

select value 
from 
STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',') 
WHERE value LIKE 'le%'

Also, we can use the function in this form:

同样，我们可以使用以下形式的函数：

USE AdventureWorks2014
GO
 
select * from HumanResources.Employee 
WHERE [jobtitle] IN 
(select value from string_split('Chief Executive Officer , Design Engineer',','))

Now, we will look at the execution plan with help of ApexSQL Plan. We can see the Table valued function operator in the execution plan.

现在，我们将借助ApexSQL Plan查看执行计划。 我们可以在执行计划中看到表值函数运算符。

When we hover over the table-valued function operator in the execution plan, we can find out all the details about this operator. Under the object label, the STRING_SPLIT function can be seen. These all details tell us that this function is a table-valued function.

当我们将鼠标悬停在执行计划中的表值函数运算符上时，我们可以找到有关该运算符的所有详细信息。 在对象标签下，可以看到STRING_SPLIT函数。 所有这些细节告诉我们，该函数是一个表值函数。

STRING_SPLIT和ORDER BY (STRING_SPLIT and ORDER BY)

Another requirement which we need in the SELECT statements is sorting functionality. We can sort the output of this function which looks like the other T-SQL statements.

我们在SELECT语句中需要的另一个要求是排序功能。 我们可以对该函数的输出进行排序，该输出类似于其他T-SQL语句。

select value 
from 
STRING_SPLIT('apple,banana,lemon,kiwi,orange,coconut',',')
order by value

Note: When I reviewed some customer feedback about SQL Server, I came across a suggestion about the STRING_SPLIT function which is “The new string splitter function in SQL Server 2016 is a good addition but it needs an extra column, a ListOrder column which denotes the order of the split values.” In my thought, this feature can be very useful for this function and I voted for this suggestion.

注意 ：当我查看了一些有关SQL Server的客户反馈时，发现了 有关STRING_SPLIT函数 的 建议 ，即“ SQL Server 2016中的新字符串拆分器函数是不错的补充，但它需要一个额外的列，即ListOrder列，它表示分割值的顺序。” 在我看来，此功能对于该功能可能非常有用，我对该建议投了赞成票。

STRING_SPLIT和加入： (STRING_SPLIT and JOIN: )

We can combine the function result set to the other table with the JOIN clause.

我们可以使用JOIN子句将函数结果集组合到另一个表中。

USE [AdventureWorks2014]
GO
 
SELECT
      [PersonType]
      ,[NameStyle]
     ,[FirstName]
      ,[MiddleName]
      ,[LastName]
   FROM [Person].[Person] P
INNER JOIN string_split('Ken,Terri,Gail',',')
on P.FirstName=value

Also, we can use CROSS APPLY function to combine the STRING_SPLIT function result set with other tables. CROSS APPLY function provides us to join table value function output to other tables.

另外，我们可以使用CROSS APPLY函数将STRING_SPLIT函数结果集与其他表组合。 CROSS APPLY函数为我们提供了将表值函数输出连接到其他表的功能。

In the following sample, we will create two tables and first table (#Countries) stores name and the continent of countries and second table (#CityList) stores city of countries table but the crucial point is #CityList table stores the city names as a string array which is separated by a comma. We will join this to the table over country columns and use the CROSS APPLY function to transform city array into a column. The below image can illustrate what will we do.

在以下示例中，我们将创建两个表，第一个表（#Countries）存储名称和国家/地区和大陆，第二个表（#CityList）存储国家/地区的城市表，但关键是#CityList表将城市名称存储为字符串数组，以逗号分隔。 我们将其与国家/地区列上的表连接起来，并使用CROSS APPLY函数将城市数组转换为列。 下图可以说明我们将做什么。

DROP TABLE IF EXISTS #Countries
GO
DROP TABLE IF EXISTS #CityList
GO
CREATE TABLE #Countries
(Continent VARCHAR(100),
Country VARCHAR(100))
GO
CREATE TABLE #CityList
(Country VARCHAR(100),
City VARCHAR(5000))
GO
INSERT INTO  #Countries
VALUES('Europe','France'),('Europe','Germany') 
 
INSERT INTO #CityList
VALUES('France','Paris,Marsilya,Lyon,Lille,Nice'), ('Germany','Berlin,Hamburg,Munih,Frankfurt,Koln')
 
SELECT 
CN.Continent,CN.Country,value
FROM #CityList CL CROSS APPLY string_split(CL.City,',')  INNER JOIN 
#Countries CN ON  CL.Country = CN.Country
 
 
DROP TABLE IF EXISTS #Countries
GO
DROP TABLE IF EXISTS #CityList
GO

有关STRING_SPLIT的更多详细信息 (More details about STRING_SPLIT)

After all the basic usage methodology of this function; we will delve into more detail. In the previous examples we always used a comma (,) as a separator for the function, however, we may need to use other symbols as a separator. The STRING_SPLIT function allows us to use other symbols as a separator, but it has one limitation about this usage. According to the MSDN; separator is a single data type and this parameter data types can be nvarchar (1), char (1), and varchar (1). Now, we will make a sample about it. The following SELECT statement will run without error. As well as we will use at (@) instead of a comma (,).

毕竟此功能的基本用法是； 我们将深入研究。 在前面的示例中，我们始终使用逗号（，）作为函数的分隔符，但是，我们可能需要使用其他符号作为分隔符。 STRING_SPLIT函数允许我们将其他符号用作分隔符，但是对此用法有一个限制。 根据MSDN； 分隔符是单个数据类型，此参数数据类型可以是nvarchar（1），char（1）和varchar（1）。 现在，我们将对此做一个样本。 以下SELECT语句将正确运行。 以及我们将使用（@）而不是逗号（，）。

DECLARE @STRINGLIST AS VARCHAR(1000)='apple,banana,lemon,kiwi,orange,coconut'
DECLARE @SEPERATOR VARCHAR(1)=','
select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

However, the following SELECT statement will return an error because of the data type declaration.

但是，由于数据类型声明，以下SELECT语句将返回错误。

DECLARE @STRINGLIST AS VARCHAR(1000)='apple@+@banana@+@lemon@+@kiwi@+@orange@+@coconut'
DECLARE @SEPERATOR VARCHAR(3)='@+@'
select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

“Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’”. The definition of error is very clear and it indicates a problem that is related to the data type of separator. After this sample, a question can appear in your mind. Can we assign NULL value to separator? We will test and learn.

“过程期望类型为'nchar（1）/ nvarchar（1）'的参数'分隔符'”。 错误的定义非常清楚，它指示与分隔符的数据类型有关的问题。 在此示例之后，您的脑海中就会浮现一个问题。 我们可以为分隔符分配NULL值吗？ 我们将进行测试和学习。

DECLARE @STRINGLIST AS VARCHAR(1000)='apple,banana,lemon,kiwi,orange,coconut'
DECLARE @SEPERATOR VARCHAR(1)=NULL
select * from STRING_SPLIT(@STRINGLIST,@SEPERATOR)

We cannot assign NULL value to separator as a value.

我们不能将NULL值分配给分隔符作为值。

In addition, when we use this function for numerical values, the result set will be in string data types. When we execute the following query, we can see all details and result in set table data type.

另外，当我们将此函数用于数值时，结果集将为字符串数据类型。 当我们执行以下查询时，我们可以看到所有详细信息并得到设置表数据类型的结果。

DROP TABLE IF EXISTS  TempStringSplit
 
SELECT VALUE INTO TempStringSplit FROM string_split('1,2,3',',') AS StrSplit
 
SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
 FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TempStringSplit'
 
DROP TABLE IF  EXISTS TempStringSplit

Now we will analyze the following query execution plan with ApexSQL Plan.

现在，我们将使用ApexSQL Plan分析以下查询执行计划。

DROP TABLE IF EXISTS  TempNumerical
GO
CREATE TABLE TempNumerical
(NumberId INT)
INSERT INTO Numerical 
VALUES ( 1),(2),(3),(5)
GO
SELECT * FROM Numerical
INNER JOIN string_split('1,2,3',',') AS StrSplit
ON Numerical.NumberId = StrSplit.value

In the select operator, you are seeing a warning sign and now find out the details about this warning.

在选择运算符中，您会看到一个警告标志，现在可以找到有关此警告的详细信息。

The reason for this warning is that we tried to join integer data type to varchar data type, so this type of usage causes implicit conversion. Implicit conversions affect the performance query.

发出此警告的原因是我们试图将整数数据类型连接到varchar数据类型，因此这种用法会导致隐式转换。 隐式转换会影响性能查询。

结论 (Conclusion)

In this article, we mentioned usage methods and all aspects of STRING_SPLIT functions. This function has very basic usage and it helps to convert arrays to columns. Also, when we compare this built-in function to other customer user-defined functions it dramatically improves the performance of queries.

在本文中，我们提到了使用方法以及STRING_SPLIT函数的所有方面。 此函数有非常基本的用法，它有助于将数组转换为列。 此外，当我们将此内置函数与其他客户用户定义的函数进行比较时，它可以极大地提高查询的性能。

翻译自: https://www.sqlshack.com/the-string-split-function-in-sql-server/