Find index of last occurrence of a sub-string using T-SQL

Straightforward way? No, but I've used the reverse. Literally.

In prior routines, to find the last occurence of a given string, I used the REVERSE() function, followed CHARINDEX, followed again by REVERSE to restore the original order. For instance:

SELECT
   mf.name
  ,mf.physical_name
  ,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
 from sys.master_files mf

shows how to extract the actual database file names from from their "physical names", no matter how deeply nested in subfolders. This does search for only one character (the backslash), but you can build on this for longer search strings.

The only downside is, I don't know how well this will work on TEXT data types. I've been on SQL 2005 for a few years now, and am no longer conversant with working with TEXT -- but I seem to recall you could use LEFT and RIGHT on it?

Philip

If you are using Sqlserver 2005 or above, using REVERSE function many times is detrimental to performance, below code is more efficient.

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

-- text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before
-- text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After
-- the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) + 1 AS LastOccuredAt

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

This answer uses MS SQL Server 2008 (I don't have access to MS SQL Server 2000), but the way I see it according to the OP are 3 situations to take into consideration. From what I've tried no answer here covers all 3 of them:

1. Return the last index of a search character in a given string.
2. Return the last index of a search sub-string (more than just a single character) in a given string.
3. If the search character or sub-string is not in the given string return 0

The function I came up with takes 2 parameters:

@String NVARCHAR(MAX) : The string to be searched

@FindString NVARCHAR(MAX) : Either a single character or a sub-string to get the last index of in @String

It returns an INT that is either the positive index of @FindString in @String or 0 meaning that @FindString is not in @String

Here's an explanation of what the function does:

1. Initializes @ReturnVal to 0 indicating that @FindString is not in @String
2. Checks the index of the @FindString in @String by using CHARINDEX()
3. If the index of @FindString in @String is 0, @ReturnVal is left as 0
4. If the index of @FindString in @String is > 0, @FindString is in @String so it calculates the last index of @FindString in @String by using REVERSE()
5. Returns @ReturnVal which is either a positive number that is the last index of @FindString in @String or 0 indicating that @FindString is not in @String

Here's the create function script (copy and paste ready):

CREATE FUNCTION [dbo].[fn_LastIndexOf] 
(@String NVARCHAR(MAX)
, @FindString NVARCHAR(MAX))
RETURNS INT
AS 
BEGIN
    DECLARE @ReturnVal INT = 0
    IF CHARINDEX(@FindString,@String) > 0
        SET @ReturnVal = (SELECT LEN(@String) - 
        (CHARINDEX(REVERSE(@FindString),REVERSE(@String)) + 
        LEN(@FindString)) + 2)  
    RETURN @ReturnVal
END

Here's a little bit that conveniently tests the function:

DECLARE @TestString NVARCHAR(MAX) = 'My_sub2_Super_sub_Long_sub1_String_sub_With_sub_Long_sub_Words_sub2_'
, @TestFindString NVARCHAR(MAX) = 'sub'

SELECT dbo.fn_LastIndexOf(@TestString,@TestFindString)

I have only run this on MS SQL Server 2008 because I don't have access to any other version but from what I've looked into this should be good for 2008+ at least.

Enjoy.

Reverse both your string and your substring, then search for the first occurrence.

I know that it will be inefficient but have you considered casting the text field to varchar so that you can use the solution provided by the website you found? I know that this solution would create issues as you could potentially truncate the record if the length in the text field overflowed the length of your varchar (not to mention it would not be very performant).

Since your data is inside a text field (and you are using SQL Server 2000) your options are limited.

This code works even if the substring contains more than 1 character.

DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words'
DECLARE @FindSubstring VARCHAR(5) = '_sub_'

-- Shows text before last substing
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) - LEN(@FindSubstring) + 1) AS Before
-- Shows text after last substing
SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) -1) AS After
-- Shows the position of the last substing
SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) AS LastOccuredAt

handles lookinng for something > 1 char long. feel free to increase the parm sizes if you like.

couldnt resist posting

drop function if exists lastIndexOf
go 
create function lastIndexOf(@searchFor varchar(100),@searchIn varchar(500))
returns int
as
begin 

if LEN(@searchfor) > LEN(@searchin) return 0 
declare @r varchar(500), @rsp varchar(100)
select @r = REVERSE(@searchin)
select @rsp = REVERSE(@searchfor)
return len(@searchin) - charindex(@rsp, @r) - len(@searchfor)+1
end 

and tests

select dbo.lastIndexof('greg','greg greg asdflk; greg sadf' )  -- 18
select dbo.lastIndexof('greg','greg greg asdflk; grewg sadf' )  --5
select dbo.lastIndexof(' ','greg greg asdflk; grewg sadf' ) --24

This thread has been going for a while. I'll offer a solution covering different basis with example:

declare @aStringData varchar(100) = 'The quick brown/fox jumps/over the/lazy dog.pdf'
/*
The quick brown/fox jumps/over the/lazy dog.pdf
fdp.god yzal/eht revo/spmuj xof/nworb kciuq ehT
*/

select
    Len(@aStringData) - CharIndex('/', Reverse(@aStringData)) + 1 [Char Index],
    -- Get left side of character, without the character '/'
    Left(@aStringData, Len(@aStringData) - CharIndex('/', Reverse(@aStringData))) [Left excluding char],
    -- Get left side of character, including the character '/'
    Left(@aStringData, Len(@aStringData) - CharIndex('/', Reverse(@aStringData)) + 1) [Left including char],
    -- Get right side of character, without the character '/'
    Right(@aStringData, CharIndex('/', Reverse(@aStringData)) - 1) [Right including char]

To get char position, need to reverse the string as CharIndex gets the first occurrence. Remembering as we're reversing, the CharIndex cursor will land on the other side of the character we're finding. So expect to compensate by -1 or +1, depending if wanting to get left or right side portion of string.