The STRING_SPLIT function in SQL Server is used to split a delimited string into multiple rows based on a specified delimiter. This function returns a single-column table whose rows are the substrings.
Here's the syntax for using STRING_SPLIT:
STRING_SPLIT ( string_value, delimiter )
- string_value: The string that you want to split.
- delimiter: The character that separates the substrings in the string.
Example
Let's say you have a string containing fruits separated by commas:
DECLARE @Fruits VARCHAR(100) = 'Apple,Orange,Banana,Grapes';
To split this string into rows, you would use the following query:
SELECT value
FROM STRING_SPLIT(@Fruits, ',');
Output
The output will look like this:
value
-------
Apple
Orange
Banana
Grapes
Notes
1. Database Compatibility: The STRING_SPLIT function became available starting with SQL Server 2016.
2. Order of Results: The results returned by STRING_SPLIT are not guaranteed to be in the original order of the substrings. If you need to maintain the order, you might have to implement a different approach, such as using a custom split function or adding an index.
3. Handling Empty Values: If your string has consecutive delimiters or begins/ends with delimiters, STRING_SPLIT will return empty strings as values. You can filter those out if necessary. For example:
SELECT value
FROM STRING_SPLIT(@Fruits, ',')
WHERE value <> '';
This is how you can use the STRING_SPLIT function to effectively split a delimited string into rows in SQL Server.