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:
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:
–> 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:
Is this available in 2012 ?? This is pretty helpful ??
no @theonlysup, its a new feature in SQL 2016.
You will need to upgrade to SQL 2016 from 2012.
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.