Is there any function to retrieve LastIndex.
-
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 -
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/ -
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
-
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 -
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.
-
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 :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+'X') - @ndx - len(@strChar+'X') + 2
end
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply