STRING_SPLIT function is an in-built table valued introduced in SQL Server 2016. The purpose of the function is to split a delimited value into multiple values based on delimiter and display result in Tabular Form.
In earlier versions, we have to create our own user defined split function for the same.
Refer this post for earlier version : Split Function in SQL
Syntax
STRING_SPLIT Function takes two parameters
Returns a single-column table with column name as value.
Let's take an example to split a comma delimited value into multiple values on the basis of delimiter (comma) using STRING_SPLIT function.
Now let's take another example where we have multiple delimited value stored in a table against an ID and each value needs to split on the basis of delimiter.
We will use Cross Apply clause in the example.
In earlier versions, we have to create our own user defined split function for the same.
Refer this post for earlier version : Split Function in SQL
Syntax
STRING_SPLIT (string, separator)
STRING_SPLIT Function takes two parameters
- String as Delimited Value: The delimited string value of any character type to be split in multiple values.
- Delimiter/Separator: The single character delimiter like comma, colon, semicolon, pipe etc. on the basis of which delimited value to be split.
Returns a single-column table with column name as value.
Let's take an example to split a comma delimited value into multiple values on the basis of delimiter (comma) using STRING_SPLIT function.
SELECT * FROM dbo.string_split('val1,val2,val3', ',')
Now let's take another example where we have multiple delimited value stored in a table against an ID and each value needs to split on the basis of delimiter.
We will use Cross Apply clause in the example.
DECLARE @TAB TABLE( id int, list varchar(100) ) INSERT INTO @TAB SELECT 1, 'apple;banana;grapes;orange' UNION ALL SELECT 2, 'potato;onion;carrot;brinjal' SELECT * FROM @TAB SELECT t.id, s.value as val FROM @TAB t CROSS APPLY dbo.string_split(t.list, ';') s