listFind function in MS SQL 2005

The following function locates the position of an element in a delimited list

ALTER FUNCTION [dbo].[listFind]

(

@List nvarchar(max),

@Element nvarchar(150),

@Delimiter nvarchar(10) = ','

)

RETURNS smallint

AS

BEGIN

declare @IndexPos int

declare @CurrentElement nvarchar(150)

declare @I int

declare @TemList nvarchar(max)

set @TemList = @list

if (len(@List)<=0)

return 0

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

set @I = 0

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

Begin

set @I = @I + 1

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

if (@IndexPos <> 0)

begin

set @CurrentElement = substring(@temlist,0,@IndexPos)

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

end

else

set @CurrentElement = @TemList

if (@CurrentElement = @Element)

return @i

end

if (@CurrentElement = @Element)

return @i

return 0

END

for example

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','dog',',')

return 1

select [dbo].[fn_v_listFind]('dog,cat,fish,cow','bird',',')

return 0