Lazycoder

13May/082

Dealing with comma delimited data in a database column

For various reasons, developers through the years have decided to store lists of data as comma-separated lists. Most programming languages include a split() function that allows you to break apart a list of data using a specified character. T-SQL does not.

I don't remember where I got this split function from. I know I didn't write all of it from scratch. But basically what it will do is take a delimited list of data, split it apart, and return a table where each data element is associated with the identity value you pass into the function. This is very useful if you just want to get the values for a single row.

PLAIN TEXT
CODE:
  1. CREATE  function Split
  2. (
  3.     @list varchar(8000),
  4.     @identityVal int,
  5.     @SplitChar char(1)
  6. )
  7. RETURNS @RetVal table
  8. (
  9.     id int,
  10.     Value nvarchar(1000)
  11. )
  12. AS
  13. BEGIN
  14.     WHILE (Charindex(@SplitChar,@list)>0)   
  15.     BEGIN
  16.         Insert Into @RetVal (id,Value)
  17.         Select @identityVal,value = (ltrim(rtrim(Substring(@list,1,charindex(@SplitChar,@list)-1))))
  18.         SET @list = Substring(@List,Charindex(@SplitChar,@list)+len(@SplitChar),len(@list))
  19.     END
  20.     INSERT INTO @RetVal (id,Value)
  21.     SELECT @identityVal, ltrim(rtrim(@list))
  22.     RETURN
  23. END

So what happens if you are trying to build a view or a report and need to split apart multiple rows. You can iterate over the rows in the table you want to flatten out and call the split function for each row, passing the rows PK into the split function.

PLAIN TEXT
CODE:
  1. create function fnFlattenMyTable
  2. ()
  3. RETURNS @RetVal table
  4. (
  5.     PrimaryKeyId int,
  6.     MyListValue varchar(100)
  7. )
  8. AS
  9. BEGIN
  10. Declare @PrimaryKeyId int
  11. Declare @MyListColumn varchar(6000)
  12.  
  13.  
  14. SET @PrimaryKeyId = (select MIN(PrimaryKeyId ) FROM MyTable)
  15. WHILE @PrimaryKeyId  is not null --main loop through MyTable
  16. BEGIN
  17.     Set @MyListColumn = (select MyListColumn from MyTable where PrimaryKeyId  = @PrimaryKeyId )
  18.     insert into @RetVal(PrimaryKeyId , MyListValue )
  19.     select id, value from [Split](@MyListColumn , @PrimaryKeyId , ',')
  20.     SET @PrimaryKeyId  = (select MIN(PrimaryKeyId ) FROM MyTable where PrimaryKeyId > @PrimaryKeyId )
  21. END
  22.  
  23. RETURN
  24. END

This function uses one of my favorite sql tricks. It uses a while loop to avoid having to declare a cursor . The logic is pretty simple, on each loop, select the lowest value of the PK in the table that is higher than the previous PK. That's the roundabout way of saying, "Select the next highest PK from the table".

Right now, this function is hard coded to flatten the table "MyTable", but it could be re-factored into a general use function.

Tagged as: , 2 Comments
   

The Lounge

TwitterCounter

120x60-1

What I'm Doing...

Powered by Twitter Tools

Books I’ve worked on

Contributors

Links

Delicious  FriendFeed  Last.fm  LinkedIn  

RSS Herding Code Podcast