SQL Server 2016 New String Split Function

With the release of SQL Server 2016 RC0, 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 into column 2 out as separate values using code like that shown below.

;WITH CTE AS (SELECT        UPPER('Adatis') as Brand_Code
                           ,CAST ('' + REPLACE('1,2,3,4,5,6', ',', '') + '' 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,',')

The Results:

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

Before:

SELECT BrandCode, LTRIM(RTRIM(Value))
FROM    StringSplitExample a
CROSS APPLY STRING_SPLIT(a.OrderID,',')
WHERE    BrandCode = 'Other2'

After: