October 7, 2007 at 8:42 am
Comments posted to this topic are about the item Function To Retrieve Last Index
Prasad Bhogadi
www.inforaise.com
December 21, 2009 at 5:47 pm
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
July 25, 2011 at 9:31 am
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
April 22, 2014 at 6:39 am
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