SQL Server 2016 Split String Function STRING_SPLIT
Transact-SQL STRING_SPLIT function is used to split string expressions using defined seperator character which is new with SQL Server 2016. For splitting character type variables in previous versions of SQL Server, T-SQL developers have created their own user defined functions to split string. With SQL Server 2016, developers can use built-in native SQL String_Split function.
Here is a very simple use of SQL Server String_Split function
select * --[value]
from STRING_SPLIT( 'Split string function in SQL Server 2016' , ' ' )
And developers can see the returned output from execution of above SQL Select statement sample with split string function which returns each word in a given sentence
SQL query sample with SQL Server String_Split() function
It is important that the splitted string pieces are returned in the order they exist in the original string expression. In other words, the output of the string_split() function is sorted exactly in the order each piece has in the source character expression.
Please note that the SQL function STRING_SPLIT() expects two string input parameters.
First parameter is the character expression. The second parameter is seperator character which is in 1 character long.
Both parameters can be varchar, nvarchar, char or nchar data types.
As you see in above in above SQL sample, I defined the seperator character as space character
If you try to pass the seperator parameter with a value more than 1 character long or zero character, SQL Server 2016 Engine will throw an exception.
Msg 214, Level 16, State 11, Line 8
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.
declare @string nvarchar(max), @separator nvarchar(max)
select @string = '
Line 1<br />
Line 2<br />
Line 3<br />
', @separator = N'<br />'
select
[value]
from STRING_SPLIT( @string, @separator )
If there is not a value between two seperators in the character expression, returned string fragment is empty string but not NULL.
Here is a simple case with string_split() function
select [value] as number
from STRING_SPLIT( N'19;26;;44;;64;;' , ';' )
--where [value] <> ''
The way to eliminate these empty strings, you can add WHERE clause criteria in SELECT statement on SQL Server 2016 split string function output list
