Extend CHARINDEX with occurance matching

  • Comments posted to this topic are about the item Extend CHARINDEX with occurance matching

  • 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

  • Way too complicated!

    No need for any while loops just a good old Tally (Numbers) table:

    DECLARE @search VARCHAR(20)

    DECLARE @find VARCHAR(2)

    SELECT @search = '-ABCDABFGHABXYZ.', @find = 'AB'

    SELECT

    occurence = ROW_NUMBER() OVER (ORDER BY n),

    position = n ,

    SUBSTRING(@search,n,LEN(@find))

    FROM

    dbo.Tally

    WHERE

    N < LEN(@search)

    AND

    SUBSTRING(@search,n,LEN(@find)) = @find

    See the link in my sig for tally table info

  • Ok, I thought about this a bit more and I don't like the loop. So I came up with the following that requires a Tally Table (A table of integers)

    CREATE FUNCTION udfCharIndexPositionTally(@ToSearch varchar(max), @InSearch VARCHAR(max), @Occurance INT)

    -- SELECT udfCharIndexPositionTally('AB','ABDABRCTAB',2) AS

    RETURNS INT

    DECLARE @return integer

    SELECT @return = position

    FROM (SELECT Row_Number() OVER(ORDER BY n) as RowNum

    , n as position, SUBSTRING(@InSearch, n,Len(@ToSearch)) as compare

    FROM TALLY

    WHERE n>0 AND n <=Len(@InSearch)

    AND SubString(@InSearch, n, Len(@ToSearch)) = @ToSearch) t

    WHERE RowNum = @Occurance

    RETURN @return

    --

    JimFive

  • James Goodwin (4/13/2011)


    Ok, I thought about this a bit more and I don't like the loop. So I came up with the following that requires a Tally Table (A table of integers)

    Ditto!

  • DECLARE @Numbertable table

    (

    ID INT PRIMARY KEY

    )

    INSERT INTO @Numbertable

    select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)

    from sys.objects si,sys.objects s

    DECLARE @separatolog TABLE

    (

    SeparatorLogID INT,

    ID INT IDENTITY(1,1) PRIMARY KEY

    )

    DECLARE @STR varchar(100) = 'ABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDE'

    DECLARE @Separator VARCHAR(10) = ''

    DECLARE @ValueAdd VARCHAR(10) = 'DEA'

    INSERT INTO @separatolog

    select distinct CHARINDEX(@ValueAdd,@str,ID)

    from @Numbertable

    where ID <= LEN(@str) and CHARINDEX(@ValueAdd,@str,ID) > 0

    ORDER BY 1

    select ID AS 'OCCURENCE',SeparatorLogID AS 'Exact Location' from @separatolog

    Regards,
    Mitesh OSwal
    +918698619998

  • To get the number of times a particular string occurs in another string

    DECLARE @STR VARCHAR(100) = 'ABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDE'

    DECLARE @SEARCH VARCHAR(10) = 'A'

    -- TO GET THE NUMBER OF OCCURRENCE COUNT

    SELECT (LEN(@STR) - LEN(REPLACE(@STR,@SEARCH,'')))/LEN(@SEARCH) OCCURRENCECOUNT

  • Thanks for the script.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply