Extend CHARINDEX with occurance matching

/****** Object: UserDefinedFunction [dbo].[ufnGetCharIndexWithOccurance] ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnGetCharIndexWithOccurance]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnGetCharIndexWithOccurance]
GO

/****** Object: UserDefinedFunction [dbo].[ufnGetCharIndexWithOccurance] ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ====================================================================
-- Author:        Partha P Pal
-- Description:    Retrive the charindex of matching string for the desired occurance. This is an extended functionality of CHARINDEX function.
--                This function performs comparisons based on the collation of the input.             
-- ====================================================================
CREATE FUNCTION [dbo].[ufnGetCharIndexWithOccurance]
(    
     @pstrToSearch VARCHAR(max)
    ,@pstrInSearch VARCHAR(max)
    ,@pintOccurance INT 
)
RETURNS int
AS
BEGIN
    DECLARE @vCharIndex INT
    DECLARE @vintOccurance INT
    DECLARE @vLoopCounter INT
    DECLARE @vCounter INT
    DECLARE @vstrToSearch VARCHAR(max)
    DECLARE    @vstrInSearch VARCHAR(max)
    DECLARE    @vstrChopped VARCHAR(max)
    
    SELECT @vstrInSearch = @pstrInSearch
    , @vstrToSearch = @pstrToSearch
    
    -- Check which occurance it needs to be searched. If null or 0 has been passed it will assumed as 1st occurance search
    SELECT @vintOccurance = CASE WHEN @pintOccurance >= 1 THEN @pintOccurance WHEN ((@pintOccurance = 0) OR (@pintOccurance IS NULL)) THEN 1 ELSE NULL END
    
    -- Set counter for looping through occurance
    SET @vLoopCounter = 1
    
    SET @vstrChopped = ''
    
    IF @vintOccurance >= 1
        BEGIN
            -- Looping through occurance
            WHILE @vLoopCounter <= @vintOccurance 
                BEGIN                    
                    -- If match found chop the string till the position match found 
                    IF (CHARINDEX(@vstrToSearch,@vstrInSearch) > 0)
                    BEGIN                    
                        SET @vstrChopped = LTRIM(@vstrChopped + SUBSTRING(@vstrInSearch, 1, CHARINDEX(@vstrToSearch,@vstrInSearch) ) )
                    END
                    
                    -- Evaluate if the intended occurance has been found
                    IF ( @vLoopCounter = @vintOccurance)
                    BEGIN
                        -- If intended occurance is 1st one then get the character index directly
                        IF (@vLoopCounter = 1)
                        BEGIN
                            SELECT @vCharIndex = CHARINDEX(@vstrToSearch,@vstrInSearch)
                        END
                        ELSE
                        BEGIN                    
                            SELECT @vCharIndex = CHARINDEX(@vstrToSearch,@vstrInSearch)
                            -- If searched string found then return the chopped string length
                            IF ( @vCharIndex > 0)
                                BEGIN                                    
                                    SET @vCharIndex = LEN(@vstrChopped) 
                                END
                        END
                        
                        -- As matching found for the intended occurance then exit loop
                        BREAK
                    END
                    
                    -- If intended occurance is yet to be found then chop the string from where it needs to be searched
                    IF (CHARINDEX(@vstrToSearch,@vstrInSearch) > 0)
                    BEGIN
                        SELECT @vstrInSearch = SUBSTRING(@vstrInSearch, CHARINDEX(@vstrToSearch,@vstrInSearch) + 1, (LEN(@vstrInSearch) - CHARINDEX(@vstrToSearch,@vstrInSearch)))
                    END
                    ELSE
                        -- As no more occurance exists exit loop
                        BREAK
                    -- Increment the loop counter
                    SET @vLoopCounter = @vLoopCounter + 1                    
                END
        END
        
    
    RETURN @vCharIndex

END

GO