How to split delimited string into separate row

In this post, let us see how to split delimited string into separate row. 

In previous versions of SQL Server, this can be achieved using User defined function. 

When support for querying JSON data was introduced in SQL Server, using OPENJSON seems to be simpler technique to split delimited string into separate row.

Finally starting from SQL Server 2016, new built string function was introduced to do this. Let us see few examples with various delimiters:

SELECT * FROM STRING_SPLIT('SQL Server, Oracle, DB2',',')

SELECT * FROM STRING_SPLIT('SQLServer Oracle DB2',' ')

SELECT * FROM STRING_SPLIT('SQL Server|Oracle|','|')
WHERE value <> ''

This function will be very handy in SSRS scenarios where SSRS dataset gets delimited string as input from multi valued parameter.

• SSRS - Multi-valued Parameter as Stored procedure Input
• Passing comma separated values (SSRS - multivalued parameter) as input to the stored procedure 

Reference: 

• STRING_SPLIT

See Also: 

• My posts on T-SQL