SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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' , ' ' )
Code

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 Server String_Split function
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)'.

SQL String_Split() function separator

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 )
Code

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] <> ''
Code

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

SQL split string function in SQL Server 2016



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.