SQL Server Performance

LASTINDEX

Discussion in 'Contribute Your SQL Server Scripts' started by gaurav_bindlish, Jul 6, 2003.

  1. gaurav_bindlish New Member

    Sometime ago I came across a situation wherein I had to find the last occuance of a string in another string. Sql server provides a very useful function CHARINDEX for finding the first occurance. So I used the same to generate this small script.
    USE MASTER

    /****************************************************************
    Returns the starting position of the last instance of specified
    character in a character string.

    Syntax
    LASTINDEX ( expression1 , expression2 )

    Arguments

    expression1
    Is an expression containing the character to be found. expression1
    is an expression of the short character data type category.

    expression2
    Is an expression, usually a column searched for the specified sequence.
    expression2 is of the character string data type category.

    Return Types
    int
    ****************************************************************/

    CREATE FUNCTION DBO.LASTINDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    DECLARE @INDEX int,
    @START int
    SELECT @STRING = RTRIM(LTRIM(@STRING))
    SELECT @START = 0
    SELECT @INDEX = CHARINDEX(@CHAR, @STRING, @START)
    WHILE @INDEX <> 0
    BEGIN
    SELECT @START = @INDEX
    SELECT @INDEX = CHARINDEX(@CHAR, @STRING, @START+1)
    END
    RETURN (@START)
    END
    Hope you find this useful.

    Gaurav
  2. bambola New Member

    In this case you can write it like this:

    CREATE FUNCTION dbo.LAST_INDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    RETURN LEN(@STRING) - CHARINDEX(@CHAR, REVERSE(@STRING), 1 + 1)
    END

    Bambola.
  3. gaurav_bindlish New Member

    Thanks Bambola!

    Gaurav
  4. SanetteWessels New Member

    <font face="Comic Sans MS"></font id="Comic Sans MS"><font color="blue"></font id="blue"><br />I tested both functions and I am sorry to say Bambola, but your's does not return the correct value. See results:<br /><br />select dbo.LASTINDEX('Sanette','e')<br />7 [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />select dbo.LAST_INDEX('Sanette','e')<br />3 [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Regards<br />Sanette
  5. SQL_Guess New Member

    Ok - the problems was the adding of 1+1, I think.

    Try :



    CREATE FUNCTION dbo.rg2_LAST_INDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    RETURN LEN(@STRING) - CHARINDEX(@CHAR, REVERSE(@STRING)) + 1
    END

    tested :



    select dbo.rg2_LAST_INDEX('Sanette','e')
    --7


    select dbo.rg2_LAST_INDEX('Eclesiatical','e')
    --4
    select dbo.rg2_LAST_INDEX('Eclesiatical','l')
    --12
    select dbo.rg2_LAST_INDEX('Eclesiatical','c')
    --10
    select dbo.rg2_LAST_INDEX('Eclesiatical','s')
    --5


    Hey Sanette - nice to see a fellow South African out here ...

    Groete uit Kaapstad
  6. SQL_Guess New Member

    On more thought, the code is unlikely to test if a char is in the string before calling the routine, obviously. That means that Last_Index solution, and my variation, return incorrrect answers when a letter doesn't exist.

    select dbo.rg2_LAST_INDEX('Eclesiatical','z')
    returns 13!!!

    New version :



    CREATE FUNCTION dbo.rg3_LAST_INDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    declare @ans int
    if ( LEN(@STRING) - CHARINDEX(@CHAR, REVERSE(@STRING)) + 1 <= len(@string) )
    select @ans = LEN(@STRING) - CHARINDEX(@CHAR, REVERSE(@STRING)) + 1
    else
    select @ans = 0
    return (@ans)
    END

    Will return 0 if not found, as the original code does.

    CiaO
  7. bambola New Member

    You are right, Sanette.
    So to avoid calculating twice the position, SQL_Guess, I'd go with this


    CREATE FUNCTION dbo.LAST_INDEX(@STRING VARCHAR(8000), @CHAR CHAR)
    RETURNS INT
    AS
    BEGIN
    IF ISNULL(@STRING, '') = '' OR ISNULL(@CHAR, '') = ''
    RETURN 0
    DECLARE @pos int
    select @pos = LEN(@STRING) - CHARINDEX(@CHAR, REVERSE(@STRING)) + 1
    IF @pos > len(@string)
    SELECT @pos = 0
    RETURN @pos
    END
    Bambola.
  8. SQL_Guess New Member

    Dohhh!!

    I did know that doing it twice was bad, but I was trying to "...see the light ..."

    As in this case, where "many hands make LIGHT work ..."

    *grin*
  9. bambola New Member

    [<img src='/community/emoticons/emotion-4.gif' alt=':p' />] [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] [8D]<br /><br />Bambola.
  10. vidit New Member

    it is great help me more
    vidit

Share This Page