How To Use Comma Separated Value List In Sql Server

This article is applicable to all sql server versions i e.

How to use comma separated value list in sql server. For example if you have following resultset and if you want each of the value of the comma separated string from the last column in a separate row previously you had to use a very complicated function on the column which was a performance killer. This function was first available in sql server 2016 and is available on databases with a compatibility level of 130 or above how to check your database compatibility level and how to change it. Select value from string split cat dog rabbit. This means you can have your result set appear as a comma separated list a space separated list or whatever separator you choose to use. Sql server compatibility level 130 and subsequent versions support a string split function to convert delimiter separated values to rows table format.

Declare concatstring varchar 4000 select concatstring coalesce concatstring name from fruits select concatstring as fruits go. We can use the string split function to separate each value into its own row. While it s true that you could achieve this same effect prior to sql server 2017 it was a bit fiddly. The coalesce function takes a list of parameters separated by commas evaluates them and returns the value of the first of its input parameters that is not null. We will learn today how to split comma separated value string in a column using string split.

2005 2008 2012 and 2014. Let s say we have the following comma delimited list. This creates a user function that takes a comma separated value string and converts it into a table that sql does understand just pass it the sting and it works it all out. It s pretty obvious how it works the only complexity is the replace part which ensures the string is terminated with a single comma by appending one and removing all double commas from the string. For compatibility levels under 130 developers have previously done this with a user defined function which incorporates a while loop or cursor to extract the data.