Function To Retrieve Last Index

  • Comments posted to this topic are about the item Function To Retrieve Last Index

    Prasad Bhogadi
    www.inforaise.com

  • I didn't compare head to head, but I would think something like this might perform better with longer strings:

    -- NOTE: this uses the len function, so this function ignores whitespace at end also

    CREATE FUNCTION dbo.LastIndexOf(

    @char as char

    , @string as varchar(max)

    )

    RETURNS integer

    AS

    BEGIN

    SET @string = rtrim(@string)

    if len(@string) = 0

    return 0

    DECLARE @revString as varchar(max)

    DECLARE @lastIndex as integer

    SET @revString = reverse(@string)

    SET @lastIndex = charIndex(@char, @revString) -- find the last char in the reverse string

    IF @lastIndex = 0

    return 0

    SET @lastIndex = len(@string) - @lastIndex + 1 -- flip it so counting from the front

    RETURN @lastIndex

    END

  • I'm not sure a function is required for this. Have you considered:

    declare @ToFind varchar(10)

    declare @InString varchar(20)

    set @ToFind = 'llo'

    set @InString = 'Hello World, Hello World'

    select LEN(@InString) - CharIndex(reverse(@ToFind),reverse(@InString)) - 1

    This returns: 16

  • Check following script made little modification.

    declare @ToFind varchar(100)

    declare @InString varchar(200)

    set @ToFind = 'llo'

    set @InString = 'Hello World, Hello World'

    select LEN(@InString) - (CharIndex(reverse(@ToFind),reverse(@InString)) + (len(@ToFind) -2))

Viewing 4 posts - 1 through 3 (of 3 total)

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