Tag Archives: sql

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.

CREATE  function Split
(
	@list varchar(8000),
	@identityVal int,
	@SplitChar char(1)
)
RETURNS @RetVal table
(
	id int,
	Value nvarchar(1000)
)
AS
BEGIN
	WHILE (Charindex(@SplitChar,@list)>0)	
	BEGIN
		Insert Into @RetVal (id,Value)
		Select @identityVal,value = (ltrim(rtrim(Substring(@list,1,charindex(@SplitChar,@list)-1))))
		SET @list = Substring(@List,Charindex(@SplitChar,@list)+len(@SplitChar),len(@list))
	END
	INSERT INTO @RetVal (id,Value)
	SELECT @identityVal, ltrim(rtrim(@list))
	RETURN
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.

create function fnFlattenMyTable
()
RETURNS @RetVal table 
(
	PrimaryKeyId int, 
	MyListValue varchar(100)
)
AS
BEGIN
Declare @PrimaryKeyId int
Declare @MyListColumn varchar(6000)


SET @PrimaryKeyId = (select MIN(PrimaryKeyId ) FROM MyTable)
WHILE @PrimaryKeyId  is not null --main loop through MyTable
BEGIN
	Set @MyListColumn = (select MyListColumn from MyTable where PrimaryKeyId  = @PrimaryKeyId )
	insert into @RetVal(PrimaryKeyId , MyListValue )
	select id, value from [Split](@MyListColumn , @PrimaryKeyId , ',')
	SET @PrimaryKeyId  = (select MIN(PrimaryKeyId ) FROM MyTable where PrimaryKeyId  > @PrimaryKeyId )
END

RETURN
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.