Function To Retrieve Last Index

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  October 7, 2007 at 8:42 am

  #175794

  Comments posted to this topic are about the item Function To Retrieve Last Index

  Prasad Bhogadi
  www.inforaise.com

• shannonholck

  SSC Rookie

  Points: 45

  More actions

  December 21, 2009 at 5:47 pm

  #1095193

  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

• jonegerton

  Old Hand

  Points: 362

  More actions

  July 25, 2011 at 9:31 am

  #1357867

  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

• hianshul

  SSC Enthusiast

  Points: 108

  More actions

  April 22, 2014 at 6:39 am

  #1707727

  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))