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


See Also: 

No comments: