Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 30, 2006

Working with Comma Separated Data - Part 2

By Gregory A. Larsen

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM