Is there any function to retrieve LastIndex.

Question

Post reply

Is there any function to retrieve LastIndex.

Prasad Bhogadi

SSCrazy Eights

Points: 9235
More actions
March 25, 2004 at 12:34 am

#107591

Hi,
I want to get the last occurance of a string/char in a given string.
is there any implicit function for doing so similar to CAHRINDEX that returns the first occurance.

Thanks in advance
-Prasad
Prasad Bhogadi
www.inforaise.com

Viewing 6 posts - 1 through 5 (of 5 total)

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

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

Select Len('Dianesh Asanka') - CHARINDEX(reverse('an'),reverse ('Dianesh Asanka'),0) - 1

Not sure above is correct

But u can use CharIndex,reverse,Len functions to achive this

My Blog: http://dineshasanka.spaces.live.com/

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 2

I saw the script "Function To Retrieve Last Index ". I do not understand why it is so complicated. THis script does the same :

create function dbo.LastIndexOf

(

@strValue varchar(4000),

@strChar varchar(50)

)

returns integer

as

begin

declare @ndx int

set @ndx = charindex(reverse(@strChar),reverse(@strValue))

if @ndx is null OR @ndx = 0 return @ndx

return len(@strValue) - @ndx - len(@strChar) + 2

end

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Hi Bert,

After I posted that question, I wrote the above script and thought it may be useful for some more people, so posted it in scripts because I didnot find any direct function which accomplishes the said functionality, however any optimizations would be appreciated.

Thanks

Prasad Bhogadi
www.inforaise.com

PhilPacha Hall of Fame Points: 3645 More actions · Answer 4

One caveat: There may be leading or trailing blank(s) in a string. In that case LEN() of the original string could differ from the LEN() of the reversed string. Use DATALENGTH() instead for correctness.

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 5

DATALENGTH returns the number of bytes, and not the number of characters ( this is not the same ) But Len returns : "Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks". To make sure there are no trailing blanks, I propose this solution :