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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

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

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 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 />'
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

SQL split string function in SQL Server 2016

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems