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 three SQL Server udf's provide equivalent functionality to the ColdFusion list functions listfirst, listgetat, and listlast.  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_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