As a SQL Server Database Administrator, you may come across applications that store data as comma-separated values in a single column. Dealing with this de-normalized data can be challenging, but SQL Server 2016 introduced a new built-in function called STRING_SPLIT that can make this task more efficient.

The STRING_SPLIT function takes two parameters: the string to be split and the separator character. It returns the separated values as a table, with each value in a separate row. The default data type of the value column is varchar, but it will be nvarchar if the input string data type is nvarchar or nchar.

Before using the STRING_SPLIT function, it’s important to ensure that the database compatibility level is set to 130. If the compatibility level is lower, the function will not be available. You can change the compatibility level using the ALTER DATABASE statement.

Once the compatibility level is set, you can use the STRING_SPLIT function to split comma-separated values. For example:

SELECT * FROM STRING_SPLIT('John,Jeremy,Jack', ',')

This will return a table with three rows, each containing one of the separated values.

The result returned by the STRING_SPLIT function can be filtered using the WHERE clause and ordered using the ORDER BY clause. For example, you can sort the result in ascending order:

SELECT * FROM STRING_SPLIT('John,Jeremy,Jack', ',') ORDER BY value ASC

You can also pass the parameters to the STRING_SPLIT function as variables:

DECLARE @String VARCHAR(50) = 'John,Jeremy,Jack'
DECLARE @Delimiter CHAR(1) = ','
SELECT * FROM STRING_SPLIT(@String, @Delimiter)

The separator character for the STRING_SPLIT function must always be a single character. If you try to use a multi-character separator, the function will fail. Similarly, the separator cannot be passed as NULL. If you pass a NULL value as the delimiter, the function will fail or return an empty table.

The STRING_SPLIT function can be used in various scenarios, such as cleaning de-normalized data and inserting it into another table. Instead of using cursors, you can use the CROSS APPLY method or JOIN the result of the STRING_SPLIT function with another table.

It’s worth noting that the STRING_SPLIT function is more efficient than user-defined split functions. In a comparison test, the STRING_SPLIT function performed significantly faster than a user-defined split function.

Overall, the STRING_SPLIT function is a powerful tool for efficiently splitting comma-separated values in SQL Server. It simplifies the process and improves performance, making it a valuable addition to your SQL Server toolkit.