**This blog was originally posted over at Adatis blogs**
With the release of SQL Server 2016, comes a function I have waited a long time for the string_split function. Everyone has there own different way of splitting strings, and I shall walk you through how I used to this, and how I will now be splitting strings using SQL Server 2016 RC0 onwards.
Take the following example where I have one record which contains 2 columns, my 2nd column containing multiple values which I want to split out.
Previously I would have used some string manipulation and the xml data type to split the values in column 2 out as separate values using code like that shown below.
;WITH CTE AS (SELECT UPPER('Adatis') as Brand_Code ,CAST ('<M>' + REPLACE('1,2,3,4,5,6', ',', '</M><M>') + '</M>' AS XML) AS Ord ) SELECT Cte.Brand_Code, Ord.a.value('.', 'VARCHAR(100)') as OrderID FROM CTE OUTER APPLY Ord.nodes ('/M') AS Ord(a) --Use the node method to join back to the original data
Now all I have to do is call the SPLIT_STRING function in SQL Server 2016 simply specifying the string to be split and the value to split by.
SELECT UPPER('Adatis') as Brand_Code, Value FROM string_split('1,2,3,4,5,6',',')
This works fine if you just want to split a single string, but if you have multiple values you want to split then you will need to use CROSS APPLY.
Given the table below with two columns which replicate the query above.
I would now need to only write the following query which is a lot neater and easier for anyone reviewing my work to understand. Here I am simply asking it to split out values found in the OrderID column using a comma as the value to split by.
SELECT BrandCode, Value FROM StringSplitExample a CROSS APPLY STRING_SPLIT(a.OrderID,',')
One thing to note, is that this function will not strip out any leading/trailing spaces so this will have to be handled either by using RTRIM and LTRIM or a REPLACE function. The following screenshots show the issue and my resolution
SELECT BrandCode, LTRIM(RTRIM(Value)) FROM StringSplitExample a CROSS APPLY STRING_SPLIT(a.OrderID,',') WHERE BrandCode = 'Other2'