T-SQL Tutorial

STRING_SPLIT function


About STRING_SPLIT function

SQL Server has a built-in function called STRING_SPLIT that provides a table-valued function to split a string into rows of substrings, based on a specified separator. The STRING_SPLIT function was introduced in SQL Server 2016.

STRING_SPLIT function allows you to split a string into a table of substrings using a specified separator. The separator can be a single character or multiple characters.


STRING_SPLIT syntax

The syntax for the STRING_SPLIT function is as follows:

STRING_SPLIT (string, separator)

Where string is the input string that you want to split and separator is the character or characters that will be used to split the string.


STRING_SPLIT example

Here's an example of how you would use the STRING_SPLIT function:

DECLARE @string VARCHAR(100) = 'Apple,Banana,Cherry,Date';
SELECT value FROM STRING_SPLIT(@string, ',');


This would return the following table:

value
Apple
Banana
Cherry
Date

As you can see, the STRING_SPLIT function makes it easy to take a string and turn it into a table. This can be useful when you need to work with data that is stored as a comma-separated list.

Note: The STRING_SPLIT function is only available in SQL Server 2016 or later. If you are using an earlier version of SQL Server, you will need to use a different method to split strings.