Function has a bug in it. If the @AppPos is two higher than the last match, the result is incorrect.
Example
select dbo.f_firstposition('abc,def',',',3)
go
Result:4 Incorrect
One Solution - Break when you can't find the next occurrence :
create function dbo.f_firstposition
(@Str varchar(8000),@StrSep varchar(10),@AppPos int)
returns int
begin
declare @i int
declare @ii int
set @STR=rtrim(ltrim(@Str))
set @i=1
select @ii=charindex(@StrSep,@Str)
if @i=@AppPos
return @ii
else
while @AppPos>@i
begin
if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0
select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii
else
begin
set @ii=0
break
end
set @i=@i+1
end
return @ii
end
go