Working with Comma Separated Data – Part 2

When dealing with data you run into all kinds of different situations. For each different occurrence you might need to have a different technique to produce the results you require. Last month I discussed a couple of different comma separated data situations. This month I will deal with a couple of additional circumstances that you might come across when working with comma separated data.


Displaying a Single Comma Delimited Column Built From Multiple Columns


This first example will take multiple columns on a single row, and output them as a single column, where each row column value is separated by a comma. This situation is useful when you want to merge two or more columns together in your output.


For demonstrating how to string columns together I’m going to use the following AddressDemo table:




















































Name


AddrLine1


AddrLine2


AddrLine3


City


State


Zip


John Smith


1234 Main Street


Apt. 29


NULL


San Francisco


CA


94117


Jane Doe


29 Dunbar Ave


Building 29


Suite 15


Miami


FL


33122


Sally Johnson


NULL


NULL


115th Street


Boston


MA


02116


Mike Turner


NULL


1825 Mountain Rd.


Apt 498


Denver


CO


80206


Dave Haggort


P.O. Box 7851


NULL


NULL


Redwood City


CA


94063


This table contains name and address information. As you can see each address is made up of one, two, or three address columns. Not all of the address columns are populated for each record. For some records one or two address columns are set to NULL. If fact, there is no consistancy in which address columns are NULL. Sometimes the first and second address columns are NULL and other times, the second and third address columns are NULL.


For each AddressDemo record, my example will display the Name column on one line, followed by all the address columns strung together with commas between each on the second line, finally followed by city, state and zip on the third line. Basically I’m trying to print out my data in kind of an address label format. Here is a single SELECT statement to accomplish my requirements:

select name + char(10) +
case when substring(reverse(coalesce(nullif(AddrLine1 + ‘,’,’,’),”) +
coalesce(nullif(AddrLine2 + ‘,’,’,’),”)+
coalesce(AddrLine3,”)),1,1) = ‘,’
then substring(coalesce(nullif(AddrLine1 + ‘,’,’,’),”) +
coalesce(nullif(AddrLine2 + ‘,’,’,’),”)+
coalesce(AddrLine3,”),
1,
len(coalesce(nullif(AddrLine1 + ‘,’,’,’),”) +
coalesce(nullif(AddrLine2 + ‘,’,’,’),”)+
coalesce(AddrLine3,”))-1)
else coalesce(nullif(AddrLine1 + ‘,’,’,’),”) +
coalesce(nullif(AddrLine2 + ‘,’,’,’),”)+
coalesce(AddrLine3,”) end + char(10) +
City + ‘,’ + State + ‘,’ + Zip + char(13) as ‘Address Label’
FROM AddressDemo

When I run this code against my AddressDemo table I get the following result set:


Address Label
———————————————————————–
John Smith
1234 Main Street,Apt. 29
San Francisco,CA,94117

Jane Doe
29 Dunbar Ave,Building 29,Suite 15
Miami,FL,33122

Sally Johnson
115th Street
Boston,MA,02116

Mike Turner
1825 Mountain Rd.,Apt 498
Denver,CO,80206

Dave Haggort
P.O. Box 7851
Redwood City,CA,94063-9025


Let me explain how this code works to print out the address columns on a single line with a comma between each column. First, for AddrLine1 and AddrLine2 columns there are a number of occurrence of code that looks like this:

coalesce(nullif(AddrLine1 + ‘,’,’,’),”)

All this code does is append a comma to the AddrLine1 column data. The NULLIF function determines if AddrLine1 column contains any data. If it does, then the value of the the AddrLine1 column is concatinated with a comma, if the AddrLine1 is null then a NULL is output from this function. The COALESCE clause is used to concatinate an empty string when the Address column contains a NULL. I use this logic to generate the address information of AddrLine1, and AddrLine2.


Since AddrLine3 just needs to be appended to the other two address columns without a trailing comma, the NULLIF funcation is not needed.


The CASE clause in my code block above is used to remove a trailing comma. A trailing comma occurs when AddrLine2 and AddrLine3 columns are NULL, or just AddrLine3 is NULL. If the CASE statement detects that the last character of my concatenated address string is a comma, then it removes the comma using the SUBSTRING function.


This example could be expanded to use any number of columns. For each additional column just replicate the COALESCE and NULLIF logic for each additional column.


How to Use a Comma Delimited String to Constrain a Selection Criterion


Some times your application might not need to display a comma delimited string, but instead you would like to use a comma delimited string to identify the records you want to select. There are a number of ways you can use a comma delimited string to help with your selection criteria. I will show you two different options.


For all each of my examples I will be selecting records from the following “City” table:




























Id


City


1


Seattle


2


Redmond


3


Portland


4


New York


5


Boston


6


Bloomington


7


Dallas


For the first example I will show you how to use a comma delimited string within an IN clause. Since you can’t just plug a variable containing a comma delimited string into an IN clause, you need to use dynamic SQL to help build a statement that contains an IN clause. Below is my dynamic SQL that uses a variable that contains a comma delimited value in an IN clause:

declare @cs varchar(100)
set @cs = ‘1,4,6’
declare @cmd nvarchar (100)
set @cmd = ‘select * from test.dbo.City where Id in (‘ + @cs + ‘)’
exec sp_executesql @cmd

In this example I want to select records from the City table that have an Id of 1, 4 or 6. I do that by first specifying a variable (@cs) that contains a string of Id’s I want to select, separated by commas. I then build a NVARCHAR string (@cmd) to contain the SELECT command I want to execute, where the @cs variable is concatenated (expanded) into the IN clause. I finally execute the dynamic SQL command @cmd using the sp_executesql stored procedure.


Here is another example that used a different technique to accomplish the same results as the dynamic SQL example:

declare @cs varchar(100)
declare @i int
declare @CityIds table (Id int)
set @cs = ‘1,4,6’
while len(@cs) > 0
begin
set @i = charindex(‘,’,@cs)
if @i = 0 set @i = len(@cs) + 1
insert into @CityIds select substring(@cs,1,@i-1)
set @cs = substring(@cs,@i+1,len(@cs))
end
select * from test.dbo.City where Id in (select Id from @CityIds)

In this example I first take the comma delimited string (@cs) and parse it apart to build a table variable. The while loop is used to separate out each Id in the comma delimited string. Each Id is then inserted into the table variable “@CityIds”. When the while loop completes, the comma delimited string will be completely parsed and all values of the comma delimited string will be placed in separate records within the table variable. In the last line of code above I have a SELECT statement within the IN clause. This SELECT statement within the IN clause selects all the records in the table variable, and therefore identifies the Id’s of the cities I want to select.


Another method to accomplish this would be to join the City table to the table variable @CityIds, like in the following code.

select a.* from test.dbo.City a join
@CityIds b
on a.Id = b.Id

If you want to use a comma delimited string to control your query results, then one of these methods should be useful to accomplish that. These techniques


can be used for building stored procedures, or functions that accepts a comma delimited string as input.


Conclusion


Displaying and using comma delimited strings come in many forms. Some require you to output comma delimited strings and others need to parse comma delimited strings. Hopefully this article has given you a few methods to deal with a couple of unique comma delimited situation, so next time you need work with a comma delimited situation, you will have some examples to start with.


» See All Articles by Columnist Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles