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

Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String.LastIndexOf method provides. A little googling revealed this - Function To Retrieve Last Index - but that does not work if you pass in a "text" column expression. Other solutions found elsewhere work only so long as the text you are searching for is 1 character long.

I will probably have to cook a function up. If I do so, I will post it here so you folks can look at it and maybe make use of.

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

The simplest way is....

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))

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

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

SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

Old but still valid question, so heres what I created based on the info provided by others here.

create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end

REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))  

worked better for me

This worked very well for me.

REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))

Hmm, I know this is an old thread, but a tally table could do this in SQL2000 (or any other database):

DECLARE @str CHAR(21),
        @delim CHAR(1)
 SELECT @str = 'Your-delimited-string',
        @delim = '-'

SELECT
    MAX(n) As 'position'
FROM
    dbo._Tally
WHERE
    substring(@str, _Tally.n, 1) = @delim

A tally table is just a table of incrementing numbers.

The substring(@str, _Tally.n, 1) = @delim gets the position of each delimiter, then you just get the maximum position in that set.

Tally tables are awesome. If you haven't used them before, there is a good article on SQL Server Central.

*EDIT: Removed n <= LEN(TEXT_FIELD), as you can't use LEN() on the TEXT type. As long as the substring(...) = @delim remains though the result is still correct.

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.

If you want to get the index of the last space in a string of words, you can use this expression RIGHT(name, (CHARINDEX(' ',REVERSE(name),0)) to return the last word in the string. This is helpful if you want to parse out the last name of a full name that includes initials for the first and /or middle name.

I realize this is a several years old question, but...

On Access 2010, you can use InStrRev() to do this. Hope this helps.

Some of the other answers return an actual string whereas I had more need to know the actual index int. And the answers that do that seem to over-complicate things. Using some of the other answers as inspiration, I did the following...

First, I created a function:

CREATE FUNCTION [dbo].[LastIndexOf] (@stringToFind varchar(max), @stringToSearch varchar(max))
RETURNS INT
AS
BEGIN
	RETURN (LEN(@stringToSearch) - CHARINDEX(@stringToFind,REVERSE(@stringToSearch))) + 1
END
GO

Then, in your query you can simply do this:

declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText'

select dbo.LastIndexOf(':', @stringToSearch)

The above should return 23 (the last index of ':')

Hope this made it a little easier for someone!

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.

@indexOf = <whatever characters you are searching for in your string>

@LastIndexOf = LEN([MyField]) - CHARINDEX(@indexOf, REVERSE([MyField]))

Haven't tested, it might be off by one because of zero index, but works in SUBSTRING function when chopping off from @indexOf characters to end of your string

SUBSTRING([MyField], 0, @LastIndexOf)

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

I needed to find the nth last position of a backslash in a folder path. Here is my solution.

/*
http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/30904809#30904809
DROP FUNCTION dbo.GetLastIndexOf
*/
CREATE FUNCTION dbo.GetLastIndexOf
(
  @expressionToFind 		VARCHAR(MAX)
  ,@expressionToSearch 		VARCHAR(8000)
  ,@Occurrence				INT =  1		-- Find the nth last 
)
RETURNS INT
AS
BEGIN

	SELECT	@expressionToSearch = REVERSE(@expressionToSearch)
	
	DECLARE @LastIndexOf		INT = 0
			,@IndexOfPartial	INT = -1
			,@OriginalLength	INT = LEN(@expressionToSearch)
			,@Iteration			INT = 0

	WHILE (1 = 1)	-- Poor man's do-while
	BEGIN
		SELECT @IndexOfPartial	= CHARINDEX(@expressionToFind, @expressionToSearch)

		IF (@IndexOfPartial = 0) 
		BEGIN
			IF (@Iteration = 0) -- Need to compensate for dropping out early
			BEGIN
				SELECT @LastIndexOf = @OriginalLength  + 1
			END
			BREAK;
		END
		
		IF (@Occurrence > 0)
		BEGIN
			SELECT @expressionToSearch = SUBSTRING(@expressionToSearch, @IndexOfPartial + 1, LEN(@expressionToSearch) - @IndexOfPartial - 1)
		END

		SELECT	@LastIndexOf = @LastIndexOf + @IndexOfPartial
				,@Occurrence = @Occurrence - 1
				,@Iteration = @Iteration + 1

		IF (@Occurrence = 0) BREAK;
	END

	SELECT @LastIndexOf = @OriginalLength - @LastIndexOf + 1 -- Invert due to reverse
	RETURN @LastIndexOf 
END
GO

GRANT EXECUTE ON GetLastIndexOf TO public
GO

Here are my test cases which pass

SELECT dbo.GetLastIndexOf('f','123456789\123456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 1) as indexOf	-- expect 20
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234\6789\123456789\', 3) as indexOf -- expect 5

To get the part before the last occurence of the delimiter (works only for NVARCHAR due to DATALENGTH usage):

DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC';

DECLARE @Delimiter CHAR(1) = '.';

SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));

This answer meets the requirements of the OP. specifically it allows the needle to be more than a single character and it does not generate an error when needle is not found in haystack. It seemed to me that most (all?) of the other answers did not handle those edge cases. Beyond that I added the "Starting Position" argument provided by the native MS SQL server CharIndex function. I tried to exactly mirror the specification for CharIndex except to process right to left instead of left to right. eg I return null if either needle or haystack is null and I return zero if needle is not found in haystack. One thing that I could not get around is that with the built in function the third parameter is optional. With SQL Server user defined functions, all parameters must be provided in the call unless the function is called using "EXEC" . While the third parameter must be included in the parameter list, you can provide the keyword "default" as a placeholder for it without having to give it a value (see examples below). Since it is easier to remove the third parameter from this function if not desired than it would be to add it if needed I have included it here as a starting point.

create function dbo.lastCharIndex(
 @needle as varchar(max),
 @haystack as varchar(max),
 @offset as bigint=1
) returns bigint as begin
 declare @position as bigint
 if @needle is null or @haystack is null return null
 set @position=charindex(reverse(@needle),reverse(@haystack),@offset)
 if @position=0 return 0
 return (len(@haystack)-(@position+len(@needle)-1))+1
end
go

select dbo.lastCharIndex('xyz','SQL SERVER 2000 USES ANSI SQL',default) -- returns 0
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',default) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',1) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',11) -- returns 1

I came across this thread while searching for a solution to my similar problem which had the exact same requirement but was for a different kind of database that was also lacking the REVERSE function.

In my case this was for a OpenEdge (Progress) database, which has a slightly different syntax. This made the INSTR function available to me that [most Oracle typed databases offer][1].

So I came up with the following code:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/',  ''))) AS IndexOfLastSlash 
FROM foo

However, for my specific situation (being the OpenEdge (Progress) database) this did not result into the desired behaviour because replacing the character with an empty char gave the same length as the original string. This doesn't make much sense to me but I was able to bypass the problem with the code below:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/',  'XX')) - LENGTH(foo.filepath))  AS IndexOfLastSlash 
FROM foo

---

Now I understand that this code won't solve the problem for T-SQL because there is no alternative to the INSTR function that offers the Occurence property.

Just to be thorough I'll add the code needed to create this scalar function so it can be used the same way like I did in the above examples.

  -- Drop the function if it already exists
  IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
	DROP FUNCTION INSTR
  GO
 
  -- User-defined function to implement Oracle INSTR in SQL Server
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
	DECLARE @found INT = @occurrence,
			@pos INT = @start;
 
	WHILE 1=1 
	BEGIN
		-- Find the next occurrence
		SET @pos = CHARINDEX(@substr, @str, @pos);
 
		-- Nothing found
		IF @pos IS NULL OR @pos = 0
			RETURN @pos;
 
		-- The required occurrence found
		IF @found = 1
			BREAK;
 
		-- Prepare to find another one occurrence
		SET @found = @found - 1;
		SET @pos = @pos + 1;
	END
 
	RETURN @pos;
  END
  GO

To avoid the obvious, when the REVERSE function is available you do not need to create this scalar function and you can just get the required result like this:

SELECT
  LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
FROM foo

[1]: http://www.sqlines.com/oracle/functions/instr "INSTR function"

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.