Microsoft 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))<>''