Iterate through a list in T-SQL

CrucisCo
6 Mar 2009
0

Many is the time that I have had a list, perhaps created in the front-end of a program that, was pushed through to the database and then needed to be iterated through. Essentially I wanted a simple way to do what in JScript or CSharp is a String.Split(','), returning an array of strings.

Usually I just write it into my stored procedure as and when needed, but I a few days ago I took the time to write a function that will do it. There is a section of the script that you can modify to deal with the specifics of your situation (the bit in between the lines of asterisks). In my code, I'm iterating through the list and building a table of items which my function returns. If you don't need to return anything just change the function to omit the RETURNS clause.



CREATE FUNCTION dbo.ActionItemsFromDelimitedList
(
	@Delimiter varchar(10),
	@List varchar(1000)
)
--omit this if you don't want to return anything or change if you want to return something else
RETURNS @ListTable TABLE
(
	ListItem varchar(50)
)
AS
BEGIN
	DECLARE @pos int
	SET @pos = 0
	DECLARE @ListItem varchar(50)
	SELECT @List=LTRIM(RTRIM(@List))
	--first remove any trailing comma
	WHILE(RIGHT(@List,1)=@Delimiter)
	BEGIN
		SELECT @List=LEFT(@List, LEN(@List)-1)
		SELECT @List=LTRIM(RTRIM(@List))
	END
	
	--start to loop through the list
	WHILE @pos<>LEN(@List)
	BEGIN
		SELECT @pos=CHARINDEX(@Delimiter,@List)
		IF (@pos>0)
			SELECT @ListItem = LTRIM(RTRIM(LEFT(@List, @pos-1)))
		ELSE
			SELECT @ListItem = LTRIM(RTRIM(@List))
		
		--***************************************************
		--start the per item action on ListItem if not blank
		IF @ListItem<>''
			INSERT INTO @ListTable (ListItem) VALUES (@ListItem)
		--end action on ListItem
		--***************************************************

		--now set up the list string for the next iteration
		IF (@pos>0)
			SELECT @List=RIGHT(@List, LEN(@List)-@pos)
		ELSE
			SELECT @List=''
		
		SELECT @pos=0
	END
             --omit the return if there is no RETURNS clause at the top of this script
	RETURN
END

I've also spent a fair bit of time wondering how I could easily do the opposite. That is, take a table or column of results and convert it into a list. This is easier said than done and there are a number of ways to do it - most of them quite specific to the situation in mind. Check this link out. I want a simple function (if possible) that will do the job for small recordsets (maximum a few hundred items) and I guess I'd sacrifice raw efficiency for the benefit of having an easy pluggable function that will do it for any recordset. I haven't been able to do it yet. If and when I crack it, I'll post the solution.