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.

Solution 1

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

Solution 2

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

Solution 3

The simplest way is....

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

Solution 4

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

Solution 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

View more solutions

Comments

[Raj]

Yes, casting to "varchar" is not an option as the data being processed frequently exceeds the maximum that can be held in a "varchar". Thanks for your answer though!

[Raj]

Yeah, its pretty awkward. This seems like it ought to be simple, only it isn't!

[ZygD]

...this is why SQL 2005 has varchar(max) to allow normal functions

[Raj]

Ah! so "varchar(max)" is a SQL 2005 thing, which explains why it didn't work when I tried it on SQL 2000.

[Philip Kelley]

Sorry -- I'm pretty sure I never did back when I was working with 2000, and I currently do not have access to any SQL 2000 installations.

[A-K]

Good point. I don't have 2000 now, and I cannot recall if I could do it when I did.

[Jared]

Brilliant! Never would have thought to attack this problem this way!

[Michael Petito]

Nice. I think this is effectively the same solution though as the accepted answer by gbn; you're just using a table to store the integers 1, 2, 3 etc. that are subtracted from DATALENGTH and reading from the first character forward instead of the last character back.

[Fredrik Johansson]

Nice one! I modified for my own needs: email.Substring(0, email.lastIndexOf('@')) == SELECT LEFT(email, LEN(email)-CHARINDEX('@', REVERSE(email)))

[Xilmiki]

+1 Because NOT fire error like 'Invalid length parameter passed to the LEFT or SUBSTRING function' if no match was found

[Chris]

Clever stuff like this is why programming is so fun!

[Andrius Naruševičius]

If your [expr] is longer than 1 symbol, you need to reverse it too!

[Tequila]

DATALENGTH fails to produce the correct result for me, though LENGTH works.

[rbsdca]

@Tequila and others: DATALENGTH returns the number of bytes not characters. Therefore, DATALENGTH returns 2 x the number of characters in a string for NVARCHAR strings. LEN, however, returns the number of characters, minus any trailing whitespace. I never use DATALENGTH for character length calculation unless trailing whitespace is significant and I know for sure that my datatypes are consistent, whether they be VARCHAR or NVARCHAR

[pkExec]

It might seem obvious in hindsight, but if you are searching for a string instead of a single character, you have to do: LEN(@FilePath) - CHARINDEX(REVERSE(@FindString),REVERSE(@FilePath))

[Phil]

why not just use right instead of left on the original instead of an extra reverse

[Geoff Griswald]

Perfect solution. I performed a directory listing from a UNIX FTP into a temporary table named #CommandOutput, and there was no easy way to extract just the filenames. This does it: SELECT Readline, REVERSE(LEFT(REVERSE(Readline), CHARINDEX(' ', REVERSE(Readline)) -1)) from #CommandOutput

[Philip Kelley]

I reviewed this ten-plus years after originally posting this answer. Recommend reviewing Mptje's and Binoj's replies (which I've upvoted), they both raise valid points.

[Adrián E]

Returns -1 if char is the first character of the string