In SQL Server, the STRING_SPLIT function is used to split a string into a table of values based on a specified delimiter. This function returns a single-column table, where each row contains a value from the original string.
Here’s how you can use the STRING_SPLIT function:
Syntax
STRING_SPLIT ( string, separator )
- string: The string to be split.
- separator: The character or characters used to split the string.
Example Usage
Suppose you have a string of comma-separated values and you want to split it into individual values. Here’s how you can do that:
DECLARE @myString NVARCHAR(100) = 'apple,banana,cherry,dates';
SELECT value
FROM STRING_SPLIT(@myString, ',');
Explanation
- @myString: This variable holds the string you want to split.
- STRING_SPLIT: This function is invoked to split the string using a comma (,) as a delimiter.
- value: The returned column from the STRING_SPLIT function contains the individual values.
Result
The result of the above query will be a table with the following rows:
value
------
apple
banana
cherry
dates
Note
- The STRING_SPLIT function does not guarantee the order of the elements, so if the order is important, you may need to include additional logic to handle that (e.g., using a numbering mechanism).
- As of SQL Server 2016, the STRING_SPLIT function is supported, and earlier versions do not have this function. If you're using an earlier version, you would need to create a custom function or use other techniques like XML or recursive CTEs to split strings.