Finding Last Occurrence of Substring in SQL Server 2000

Technical Background and Problem Analysis

In SQL Server 2000 environment, string manipulation often requires locating the last occurrence of a substring. Unlike the System.String.LastIndexOf method in .NET framework, SQL Server 2000 has limited built-in function support for this functionality, especially when dealing with TEXT data types.

TEXT data type in SQL Server 2000 has special storage characteristics and limited function support scope. According to official documentation, only a restricted set of string functions can be directly applied to TEXT columns, which adds additional complexity to string processing tasks.

Limitations of Traditional Approaches

A common solution involves using REVERSE() function combined with CHARINDEX to simulate last index search functionality. For example:

SELECT LEN(@FilePath) - CHARINDEX(@FindChar, REVERSE(@FilePath)) + 1 AS LastOccuredAt

However, this approach is completely unavailable for TEXT data types in SQL Server 2000, as the REVERSE function does not support TEXT type parameters. This limitation renders many elegant solutions impractical in real-world applications.

PATINDEX-Based Solution

To address SQL Server 2000 limitations, we can employ the PATINDEX function combined with reverse search strategy. The core concept involves searching backward from the end of the string, progressively checking each possible position.

Complete implementation code:

DECLARE @SearchString TEXT, @Pattern VARCHAR(100), @DataLength INT, @Position INT
SET @SearchString = 'Your text value here'
SET @Pattern = 'search_pattern'
SET @DataLength = DATALENGTH(@SearchString)
SET @Position = @DataLength

WHILE @Position > 0
BEGIN
    IF PATINDEX(@Pattern, SUBSTRING(@SearchString, @Position, 1)) > 0
        BREAK
    SET @Position = @Position - 1
END

SELECT @Position AS LastOccurrencePosition

This algorithm uses DATALENGTH to obtain the actual string length, then checks each character from the end until finding a matching pattern or reaching the beginning of the string.

Performance Optimization and Edge Case Handling

In practical applications, various edge cases need consideration:

• Empty string handling: When DATALENGTH returns 0, directly return 0 or NULL
• No match found: When loop completes without finding a match, return 0
• Performance considerations: For longer TEXT data, recommend adding maximum search count limits

Optimized code example:

CREATE FUNCTION dbo.FindLastIndex(@InputText TEXT, @SearchPattern VARCHAR(255))
RETURNS INT
AS
BEGIN
    DECLARE @DataLen INT, @CurrentPos INT, @FoundPos INT
    SET @DataLen = DATALENGTH(@InputText)
    SET @CurrentPos = @DataLen
    SET @FoundPos = 0
    
    WHILE @CurrentPos > 0 AND @FoundPos = 0
    BEGIN
        IF PATINDEX(@SearchPattern, SUBSTRING(@InputText, @CurrentPos, 1)) > 0
            SET @FoundPos = @CurrentPos
        ELSE
            SET @CurrentPos = @CurrentPos - 1
    END
    
    RETURN @FoundPos
END

Version Compatibility Comparison

With SQL Server version upgrades, string processing capabilities have significantly improved:

• SQL Server 2005 and later support REVERSE function for VARCHAR(MAX) type
• SQL Server 2012 introduced more powerful string functions like STRING_SPLIT
• Modern versions recommend using VARCHAR(MAX) instead of TEXT data type

For environments still using SQL Server 2000, the PATINDEX-based solution provided in this paper remains the most reliable approach.

Practical Application Scenarios

This last occurrence search functionality has important applications in various scenarios:

• File path parsing: Extracting filenames from full paths
• Log analysis: Finding last occurrence of specific error codes
• Data cleaning: Locating and replacing specific patterns in strings
• Text processing: Implementing custom string splitting and extraction logic

Through proper encapsulation and optimization, this solution can provide stable functional support while maintaining performance.