Extend CHARINDEX with occurance matching

,

Often in our day to day sql query activities we require to retrieve the CHARINDEX from string value which we easily can achieve using tsql CHARINDEX . Requirements often extends to the scenario that we need to consider 1st occurance only or 2nd occurance only where we know the searched string may appear more than once in the value string. But in that case usage of CHARINDEX becomes bit complecated as we need to do lot of string manipulation to get the desired charindex position.

 

To overcome this limitation, I tried to extend the functionality of CHARINDEX with a new user defined function. This udf is actually based on 2 mostly used string functon CHARINDEX & SUBSTRING.

If we look at CHARINDEX signature it's CHARINDEX ( expression1 ,expression2 [ , start_location ] ). If we try to find 'A' from a string 'ABDABRCTAB' using CHARINDEX we need to call it as :

a) for the 1st occurance

CHARINDEX('AB','ABDABRCTAB',1)

b) for the 2nd occurance

CHARINDEX('AB','ABDABRCTAB',2) or CHARINDEX('AB','ABDABRCTAB',3) or CHARINDEX('AB','ABDABRCTAB',4)

c) for the 3rd occurance

CHARINDEX('AB','ABDABRCTAB', 5) or CHARINDEX('AB','ABDABRCTAB',6) etc

Actually we need to know before hand the where from we should start (ie start location)

 

But using my proposed udf (I've named it as ufnGetCharIndexWithOccurance) we need to call it as

ufnGetCharIndexWithOccurance ( expression1 ,expression2, intended_occurance)

where we need to search string <expression1> in string <expression2> for the occurance integer type of <intended_occurance>.

So if we reconsider the example explained above using this new udf the usage will be

a) for the 1st occurance

ufnGetCharIndexWithOccurance('AB', 'ABDABRCTAB', null) or ufnGetCharIndexWithOccurance('AB', 'ABDABRCTAB', 0) or ufnGetCharIndexWithOccurance('AB', 'ABDABRCTAB', 1)

b) for the 2nd occurance

CHARINDEX('AB','ABDABRCTAB',2)

c) for the 3rd occurance

CHARINDEX('AB','ABDABRCTAB', 3)

and so on.

Now this udf has been created keeping in mind, it will use the server Collation on which the function will be executed.

 

Sounds good !! So what's there to wait ... let's try using it and get rid off repeated usage of same code block !

 

Cheers !!

/****** 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

Rate

3.17 (6)

Share

Share

Rate

3.17 (6)