SQL- String_Split() function

This is the most awaited feature of SQL Server which is coming with SQL Server 2016. This allows to get rid of previous perversions with XML and CTE. This function returns a single-column table with fragments after the input character string is splitted by the specified separator.

Syntax STRING_SPLIT (String, Separator)  Arguments String: Is an expression of any character type (i.e. nvarchar, varchar, nchar or char). Separator: Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings. Return Types: Returns a single-column table with fragments. The name of the column is value. The length of the return type is the same as the length of the string argument.

Important Facts: There are some facts about this function as –

1. Default return data type is varchar and it will return nvarchar if any of the input arguments are either nvarchar or nchar.
2. It will return an empty table If the input string is NULL.
3. It will return empty string if there is nothing between separators. Condition RTRIM(value) <> '' will remove empty tokens.
4. STRING_SPLIT requires at least compatibility mode 130.
5. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function.
6. We can change the compatibility level of database as following: ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Examples

Split comma separated value string- Parse a comma separated list of values and return all tokens:

----Local Variables Declare @StringValue Varchar(200) Set @StringValue='Ryan Arjun, Kimmy Wany, Rosy Gray,,Johnson Blue' ---- Separator Variable Declare @Separator char(1)=',' ---- Using String Split function Select Value from string_split(@StringValue,@Separator) Value Ryan Arjun Kimmy Wany Rosy Gray Johnson Blue STRING_SPLIT will return empty string if there is nothing between separator.

Split comma separated value string- Parse a comma separated list of values and return all non-empty tokens:

----Local Variables Declare @StringValue Varchar(200) Set @StringValue='Ryan Arjun, Kimmy Wany, Rosy Gray,,Johnson Blue' ---- Separator Variable Declare @Separator char(1)=',' ---- Using String Split function Select Value from string_split(@StringValue,@Separator) ---- remove empty strings from output where RTRIM(value) <> '';  Value Ryan Arjun Kimmy Wany Rosy Gray Johnson Blue

Split comma separated value string in a column

To understand the functionality, we are using the table variable and fill some data values with tags column and using the String_Split() function to get the expected result as given below: 

----Declare table variable Declare @ProductTable Table ( ProdId Int, ProductName varchar(50), Tags Varchar(200) ) ---- Fill Data into table variable Insert into @ProductTable (ProdId, ProductName, Tags) Values (1, 'Full-Finger Gloves', 'clothing,road,touring,bike'), (2,'LL Headset','bike, cycle'), (3,'HL Mountain Frame','bike,mountain,weather') ---- Pull values from table Variable SELECT ProdId, ProductName, Tags FROM @ProductTable  ProdId ProductName Tags 1 Full-Finger Gloves clothing,road,touring,bike 2 LL Headset bike, cycle 3 HL Mountain Frame bike,mountain,weather ---- Separator Variable Declare @Separator char(1)=',' ---- Using String Split function SELECT ProdId, ProductName, Value  FROM @ProductTable  CROSS APPLY STRING_SPLIT(Tags, @Separator);  ProdId ProductName Value 1 Full-Finger Gloves clothing 1 Full-Finger Gloves road 1 Full-Finger Gloves touring 1 Full-Finger Gloves bike 2 LL Headset bike 2 LL Headset  cycle 3 HL Mountain Frame bike 3 HL Mountain Frame mountain 3 HL Mountain Frame weather ----- Aggregation by values SELECT value as tag, COUNT(*) AS [Products]  FROM @ProductTable      CROSS APPLY STRING_SPLIT(Tags, ',')  GROUP BY value  HAVING COUNT(*) > 2  ORDER BY COUNT(*) DESC; ----shows the number of products per each tag, ordered by number of products, and to filter only the tags with more than 2 products. Tag Products bike 3 ----- Search by tag value: To find products with a single tag SELECT ProdId, ProductName, Tags  FROM @ProductTable  WHERE 'bike' IN (SELECT value FROM STRING_SPLIT(Tags, ',')); ProdId ProductName Tags 1 Full-Finger Gloves clothing,road,touring,bike 2 LL Headset bike, cycle 3 HL Mountain Frame bike,mountain,weather ----- Search by tag value: To find products with a multiple tags SELECT ProdId, ProductName, Tags  FROM @ProductTable  WHERE EXISTS (SELECT *      FROM STRING_SPLIT(Tags, ',')      WHERE value IN ('clothing', 'mountain'));  ProdId ProductName Tags 1 Full-Finger Gloves clothing,road,touring,bike 3 HL Mountain Frame bike,mountain,weather

Conclusion

STRING_SPLIT takes a string that should be divided and the separator that will be used to divide string. It returns a single-column table with substrings. This function works only with a single-character delimiter and you can use it anywhere in your SQL or dynamic SQL Scripts also.