listGetAt functions in SQL 2005

The following list return the element at the X position in a delimited sting list

ALTER FUNCTION [dbo].[listGetAt]

(

@List nvarchar(max), -- delimited list

@ElementPos int, -- the element position

@Delimiter nvarchar(10) = ',', -- the delimiter string

@ReturnOnNotFound nvarchar(100) = null -- the default value if element does not exist

)

RETURNS nvarchar(100)

AS

BEGIN

declare @StrartPos int

declare @IndexPos int

declare @CurrentElement int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return @ReturnOnNotFound

set @StrartPos = 0

set @IndexPos = charIndex(@Delimiter,@List,@StrartPos)

set @CurrentElement = 0

while (@IndexPos <> 0 and @CurrentElement<@ElementPos and @TemList <> '')

Begin

set @CurrentElement = @CurrentElement + 1

set @IndexPos = charIndex(@Delimiter,@TemList,@StrartPos)

if (@IndexPos <> 0 and @CurrentElement<@ElementPos)

set @TemList = substring(@TemList,@IndexPos+1,len(@TemList))

end

if (@CurrentElement<@ElementPos)

return @ReturnOnNotFound

if @IndexPos > 0

return substring(@temlist,0,@IndexPos)

return @temlist

END

For example:

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',2,',','not found')

returns 'cat'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',1,',','not found')

returns 'dog'

select [dbo].[fn_v_listGetAt]('dog,cat,fish,cow',7,',','not found')

returns 'not found'