Microsoft SQL Server Split String function in Sql Server Split a String in Sql Server 2016
Example
In SQL Server 2016 finally they have introduced Split string function : STRING_SPLIT
Parameters: It accepts two parameters
String:
Is an expression of any character type (i.e. nvarchar, varchar, nchar or char).
separator :
Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.
Note: You should always check if the expression is a non-empty string.
Example:
Select Value
From STRING_SPLIT('a|b|c','|')
In above example
String : 'a|b|c'
separator : '|'
Result :
+-----+
|Value|
+-----+
|a |
+-----+
|b |
+-----+
|c |
+-----+
If it's an empty string:
SELECT value
FROM STRING_SPLIT('',',')
Result :
+-----+
|Value|
+-----+
1 | |
+-----+
You can avoid the above situation by adding a WHERE clause
SELECT value
FROM STRING_SPLIT('',',')
WHERE LTRIM(RTRIM(value))<>''
