Categories: SQL server 2016

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’, ‘ ‘)

 

 

SQL Server String Split function starting 2016 version

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’, ”)

SQL Server String Split function starting 2016 version

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

SQL Server String Split function starting 2016 version

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],’,’)

SQL Server String Split function starting 2016 version

 3,920 total views,  3 views today

admin

Share
Published by
admin

Recent Posts

What is slow changing dimension?

The attribute value in Dimension table are going to change slowly over time and they…

5 years ago

How to Change SQL Server Compatibility level

  Changing Database compatibility level will be required when you upgrade your database from one…

6 years ago

View Compatibility level of Database

To know what is compatibility Level check this post link. You can view the compatibility…

6 years ago

SQL Server Compatibility Level

SQL Server Compatibility level will be used to decide how SQL will use it features.…

6 years ago

View in SQL server

A view is a virtual table.  View will not store any data physically. The data…

6 years ago

SSIS :The package failed to load due to error 0xC0011008

Below is the error which I was getting today while deploying the SSIS pacakge for…

6 years ago