This code seems to be overly complicated for the task presented. Why are you saving off the Chopped part of the input? If I needed this functionality I would keep track of the current position and use something like:

CREATE FUNCTION [dbo].[ufnGetCharIndexWithOccurance](@ToSearch VARCHAR(max), @InSearch VARCHAR(max), @Occurance INT=1)

RETURNS int

AS

--SELECT dbo.ufnGetCharIndexWithOccurance('AB', 'ABDABRCTAB', 5)

BEGIN

DECLARE @LoopCounter integer

DECLARE @CurrentLocation integer

DECLARE @result integer

SET @LoopCounter = 1

SET @result = 0

While @LoopCounter <= @Occurance BEGIN

Set @result = CHARINDEX(@ToSearch, @InSearch, @result+1)

IF @result = 0 --Occurance Not found

Set @LoopCounter = @Occurance + 1

ELSE

Set @LoopCounter = @LoopCounter + 1

END

RETURN @result

END

Note: I don't validate @Occurance. If @Occurance is not a valid input the While Condition will fail and the function will return 0.

--

JimFive