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.

Accepted Answer

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

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

Solution

Problem