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

Question

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.

Answer 1

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

Answer 2

The simplest way is....

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

Answer 3

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) = '\'

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

Answer 4

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

Answer 5

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

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

Answer 6

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

Answer 7

This worked very well for me.

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

Answer 8

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

worked better for me

Answer 9

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 (Free reg, or just use Bug Me Not (http://www.bugmenot.com/view/sqlservercentral.com)).

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

Answer 10

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

Answer 11

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

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

Answer 12

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!

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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.

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

Answer 22

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