SQLServer中的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 stri
ng 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 preview150
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 R2100
SQL Server 2008100
SQL Server 2005 (9.x)90
SQL Server 200080
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 R2100
SQL Server 2008100
SQL Server 2005（9.x）90
SQL Server 200080
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',','))
java中split的用法
Now, we will look at the execution plan with help of . We can see the Table valued function operator in the execution 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 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。