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.

Solution 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

Solution 2:

The simplest way is....

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