CF-style List functions in SQL Server

The built-in string handling functions in t-sql are all well and good, but there really aren't any graceful ways of handling lists.  As any CF developer that's been around a while knows, lists are pretty commonplace in practice, and there are times when it makes more sense (or you're required) to do list handling at the db level rather than at the front end.

The following five SQL Server udf's provide equivalent functionality to the ColdFusion list functions listfirst, listgetat, listsetat, listlast, and listfind.  The only thing that differs from their CF counterparts (and a minor one at that) is that they require you to pass a single delimiter, not a set of possible delimiters as in CF.

CREATE FUNCTION [dbo].[uf_listfirst] (@list varchar(2000), @delimiter char(1))
RETURNS varchar(500) 
/* 
	Purpose: returns the first item in @list based on the character defined in @delimiter
	Author: Kevin J. Miller (www.websolete.com)
	
	Example: 
		SELECT myColumn, dbo.uf_listfirst(myColumn,'/') FROM myTable 
		would return: 
			mykingdom.com/subdir/hello/index.htm	mykingdom.com
		
 */
AS
BEGIN 
	DECLARE @listitem varchar(500), @cpos smallint; 
	SET @listitem = NULL; 
	SET @list = LTRIM(RTRIM(@list))+ @delimiter; 
	SET @cpos = CHARINDEX(@delimiter,@list, 1); 
	IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length
	BEGIN 
		SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) 
		RETURN @listitem 
	END  
	
	RETURN @listitem 
END

-- -----------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[uf_listgetat] (@list varchar(2000), @pos smallint, @delimiter char(1))
RETURNS varchar(500) 
/*
 	Purpose: returns the item at position @pos in @list based on the character defined 
	in @delimiter
	Notes: list positions are 1-based, not 0-based
	Author: Kevin J. Miller (www.websolete.com)
	
	Example: 
		SELECT myColumn, dbo.uf_listgetat(myColumn,3,'/') FROM myTable 
		would return: 
			mykingdom.com/subdir/hello/index.htm	hello
		
 */
AS
BEGIN 
	DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; 
	SET @listitem = NULL; 
	SET @list = LTRIM(RTRIM(@list))+ @delimiter; 
	SET @cpos = CHARINDEX(@delimiter,@list, 1); 
	SET @loop = 0; 
	IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length
	BEGIN
		WHILE @cpos > 0
		BEGIN
			SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) 
			SET @loop = @loop + 1
			IF @loop = @pos RETURN @listitem  
			SET @list = RIGHT(@list, LEN(@list) - @cpos)
			SET @cpos = CHARINDEX(@delimiter, @list, 1)
		END
	END	
	SET @listitem = NULL -- if we got here, the position specifed exceeds the list length
	RETURN @listitem 
END

-- ----------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[uf_listsetat] (@list varchar(8000), @pos smallint, @newelement varchar(2000), @delimiter char(1)) 
RETURNS varchar(8000)
/*
 	Purpose: sets the item at position @pos in @list based on the character defined 
	in @delimiter
	Notes: list positions are 1-based, not 0-based
	Author: Bud Schneehagen - based upon functions originally from Kevin J. Miller's (www.websolete.com)
	
	Example: 
		SELECT myColumn, dbo.f_ListSetAt(myColumn,3,'bye','/') FROM myTable 
		would return: 
			mykingdom.com/subdir/hello/index.htm	mykingdom.com/subdir/bye/index.htm
		
 */
AS
BEGIN 
	DECLARE @newlist varchar(8000), @cpos smallint, @loop smallint; 
	SET @newlist = NULL;
	SET @list = LTRIM(RTRIM(@list))+ @delimiter; 
	SET @cpos = CHARINDEX(@delimiter,@list, 1); 
	SET @loop = 0; 
	IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length
	BEGIN
		WHILE @cpos > 0
		BEGIN
			SET @loop = @loop + 1
			
			IF @loop = 1
			BEGIN
				IF @loop = @pos
				SET @newlist = @newelement
				ELSE
				SET @newlist = LTRIM(RTRIM(LEFT(@list, @cpos - 1)))
			END
			ELSE
			BEGIN
				IF @loop = @pos
				SET @newlist = @newlist + @delimiter + @newelement
				ELSE
				SET @newlist = @newlist + @delimiter + LTRIM(RTRIM(LEFT(@list, @cpos - 1)))
			END
			
			SET @list = RIGHT(@list, LEN(@list) - @cpos)
			SET @cpos = CHARINDEX(@delimiter, @list, 1)
		END
	END	
	RETURN @newlist
END

-- ----------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[uf_listlast] (@list varchar(2000), @delimiter char(1))
RETURNS varchar(500) 
/* 
	Purpose: returns the last item in @list based on the character defined in @delimiter
	Author: Kevin J. Miller (www.websolete.com)
	
	Example: 
	SELECT myColumn, dbo.uf_listlast(myColumn,'/') FROM myTable 
	would return: 
	mykingdom.com/subdir/hello/index.htm	index.htm
	
 */
AS
BEGIN 
	DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; 
	SET @listitem = NULL; 
	SET @list = LTRIM(RTRIM(@list))+ @delimiter; 
	SET @cpos = CHARINDEX(@delimiter,@list, 1); 
	SET @loop = 0; 
	IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length
	BEGIN
	WHILE @cpos > 0
	BEGIN
	SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) 
	SET @loop = @loop + 1 
	SET @list = RIGHT(@list, LEN(@list) - @cpos)
	SET @cpos = CHARINDEX(@delimiter, @list, 1)
	END
	END	
	RETURN @listitem 
END


-- ----------------------------------------------------------------------------------------

 CREATE FUNCTION [dbo].[uf_listfind] (@list varchar(2000), @item varchar(500), @delimiter char(1))
 RETURNS smallint 
 /*
     Purpose: returns the position of @item in @list based on the character defined 
        in @delimiter
     Notes: list positions are 1-based, not 0-based; case-sensitivity will be based on 
        the server collation (most likely will be case insensitive on most default installs)
     Author: Kevin J. Miller (www.websolete.com)
    
     Example: 
         SELECT myColumn, dbo.uf_listfind(myColumn,'admin','/') FROM myTable 
         would return: 
             mykingdom.com/admin/hello/index.htm        2
        
 */
 AS
 BEGIN 
    DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; 
    SET @listitem = NULL; 
    SET @list = LTRIM(RTRIM(@list))+ @delimiter; 
    SET @cpos = CHARINDEX(@delimiter,@list, 1); 
    SET @loop = 0; 
    IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length
    BEGIN
        WHILE @cpos > 0
        BEGIN
            SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) 
            SET @loop = @loop + 1
            IF @listitem = @item RETURN @loop 
            SET @list = RIGHT(@list, LEN(@list) - @cpos)
            SET @cpos = CHARINDEX(@delimiter, @list, 1)
        END
    END    
    -- if we got here, the item was not found in the list
    RETURN 0 
 END