LASTINDEX

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

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.

Thanks Bambola!

Gaurav

<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

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

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

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.

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*

[<img src='/community/emoticons/emotion-4.gif' alt='' />] [<img src='/community/emoticons/emotion-2.gif' alt='' />] [8D]<br /><br />Bambola.

it is great help me more
vidit