SQL Server String Split function available in SQL Server 2016

Starting from SQL server 2016 we have new String split function which will help to split a string in rows.

With earlier version of sql server a developer would have to create his own custom scripts to achieve this task.

Syntax : STRING_SPLIT( ‘string’,’separator’).

Split string will divide the input string using a separator into rows.

The output will be a single column table with the name as value.

Note : String_Split will at least need SQL compatibility level as 130.

Below is an example .

select VALUE FROM STRING_SPLIT( ‘Welcome to SQLAG BLOG’, ‘ ‘)

You will get the below error if you have not defined a separator.

Msg 214, Level 16, State 11, Line 16
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

select VALUE FROM STRING_SPLIT( ‘Welcome to SQLAG BLOG’, ”)

Now lets check another example on how this can be used in real business scenaro.

Consider you are receiving state and city information in the below format.

CREATE table test_StringCREATE table test_String( state varchar(20),  city varchar(50))

INSERT INTO TEST_STRING

VALUES( ‘NEW YORK’, ‘NEW YORK CITY’),

( ‘CALIFORNIA’, ‘LOS ANGELES , SAN DIEGO , SAN JOSE’),

( ‘TEXAS’, ‘DALLAS , AUSTIN’)

SELECT STATE,CITY FROM TEST_STRING

Now with the below String split function you can convert the city into each single rows.

SELECT STATE,VALUE FROM TEST_STRING
CROSS APPLY STRING_SPLIT( [CITY],’,’)

 3,684 total views,  1 views today