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 - http://www.sqlservercentral.com/scripts/T-SQL+Aids/31116/ - 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.

Thanks!

Answer 1

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 2

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 3

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

Answer 4

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 5

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 6

ALTER FUNCTION [dbo].[CountWord] ( -- Add the parameters for the function here @RowStr nvarchar(1000), @WordStr nvarchar(255) ) RETURNS int AS BEGIN

declare @WordCount int
declare @index int 

set @index =0
set @WordCount  = 0

while CHARINDEX(@WordStr, @RowStr,@index)>0 begin

SET @index =  CHARINDEX(@WordStr,@RowStr,@index)
SET @WordCount = @WordCount  + 1
if @index =0
break
else
SET @index = @index + 1 

end

RETURN @WordCount 

END