STRING_SPLIT Function

String _Split is a table valued function, which splits the string into multiple substring based on a separator character.

Syntax:

STRING_SPLIT ( string , separator )

·         Output order can be in any order. Final sort order can be overridden by order by clause.

·         Empty string can be filtered out by using where clause. ( Where value <>' ')

Below are few examples to use string_split funcction

Ex:

Select value from string_split('Red,Black,Blue',',')

Output:

value

Red

Black

Blue

Select value from string_split('Red,Black,,Blue',',')

where value<>''

Output:

value

Red

Black

Blue

use AdventureWorksDW2017

Go

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          where Color in (Select value from string_split('Red,Black,,Blue',',')

where value<>'')

Go

Can be used in join

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          join string_split('Red,Black,Blue',',') on value=Color