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