STRING_SPLIT function in SQL Server

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 (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.

Return Type
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