Working with Comma Separated Data - Part 2
October 30, 2006
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 Im going to use the following AddressDemo table:
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 Im 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:
For the first example I will show you how to use a comma delimited string within an IN clause. Since you cant 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 Ids 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 Ids 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.
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.