Home > SQL Built-in Functions, SQL Server 2016, SQL Tips > New in-built Table-Valued Function STRING_SPLIT() in SQL Server 2016 – to split strings

New in-built Table-Valued Function STRING_SPLIT() in SQL Server 2016 – to split strings


Till now it was bit tricky to split a Sentence or CSV String to multiple values or rows, and we used different logic to do the same.

In my [previous post] I blogged similar logic to Split a String and Combine back by using some XML syntax.

In SQL Server 2016 this has been made simpler by using a new function STRING_SPLIT(), let’s see this with a simple example:

SELECT * FROM STRING_SPLIT('My name is Manoj Pandey', ' ')

This will split all the words in the sentence separated by a whitespace in different rows:

STRING_SPLIT
 

Here is the syntax for the same:

STRING_SPLIT ( string , separator )
 

Please note: that the separator should be a single character expression, so this should not be an empty string, like:

SELECT * FROM STRING_SPLIT('My name is Manoj Pandey', '')

Will result into an error:

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

 

–> Let’s check one more example:

We have a comma separated Cities list for each State as a row in a table:

CREATE TABLE #tempCityState (
	[State] VARCHAR(5), 
	[Cities] VARCHAR(50)
)

INSERT INTO #tempCityState
SELECT 'AK', 'Nashville,Wynne'
UNION ALL
SELECT 'CA', 'Fremont,Hanford,Los Anggeles'
UNION ALL
SELECT 'CO', 'Aspen,Denver,Teluride,Vail'

Now, lets just use the simple function STRING_SPLIT() with CROSS APPLY operator, like:

SELECT [State], value
FROM #tempCityState
CROSS APPLY STRING_SPLIT([Cities], ',')

Will give you following output:

STRING_SPLIT 2
 

–> And if I compare the performance of this function with the earlier approach I mentioned in my [previous post]:

Run both the queries by enabling Actual Execution plan (Ctrl + M):

SELECT [State], value
FROM #tempCityState
CROSS APPLY STRING_SPLIT([Cities], ',')

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
    FROM  #tempCityState) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2

I can see that the STRING_SPLIT() gives me better performance compared to the other:
STRING_SPLIT 3


Advertisement
  1. March 10, 2016 at 9:16 am

    Is this available in 2012 ?? This is pretty helpful ??

    • March 10, 2016 at 9:20 am

      no @theonlysup, its a new feature in SQL 2016.

      You will need to upgrade to SQL 2016 from 2012.

  2. August 11, 2016 at 12:20 pm

    The function requires the database to be in compatibility level 130. So even you are in SQL Server 2016 and with earlier compatibility level(for some reason), this will not help/work.

  1. March 24, 2016 at 4:02 pm
  2. December 23, 2016 at 7:00 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: