Is there a LastIndexOf in SQL Server?

Custom function to the rescue

Sometimes, in-built functions are not enough. In such cases, we need to resort to User-Defined Functions (UDFs).

-- Just like Santa's workshop but SQL style
CREATE FUNCTION dbo.LastIndexOf(@haystack NVARCHAR(MAX), @needle NVARCHAR(MAX)) 
RETURNS INT AS
BEGIN
    IF @haystack IS NULL OR @needle IS NULL RETURN 0 -- Because NULLs are party poopers
    RETURN LEN(@haystack) - CHARINDEX(REVERSE(@needle), REVERSE(@haystack))
END
GO


Use this function, substituting your column and search term:

-- Because who does not love concise code?
SELECT dbo.LastIndexOf(your_column, 'needle') as LastIndexOf
FROM your_table


Let's banish the ghost of NTEXT and use NVARCHAR(MAX) instead, for both future compatibility and to handle leading/trailing spaces effectively.

Slicing and dicing substrings

Essential slices: after last occurrence

Got lost in your strings? The pair of functions, RIGHT() and LEN, can guide you to the substring after the last occurrence of a delimiter:

-- Jackpot!
SELECT RIGHT(your_column, LEN(your_column) - CHARINDEX(REVERSE('delimiter'), REVERSE(your_column)))
FROM your_table


Essential slices: before last occurrence

To extract the substring before the final appearance, LEFT() will be your hero:

-- Voila!
SELECT LEFT(your_column, CHARINDEX(REVERSE('delimiter'), REVERSE(your_column)) - 1)
FROM your_table


Mastering this is pivotal for parsing URLs, file paths, or your niece's secret messages.

The beauty of Common Table Expressions (CTEs)

For more complex operations, CTEs are a great way to enhance readability. They speak set language fluently, pairing well with ROW_NUMBER():

-- CTEs are like your nerdy best friend in high school
WITH SortedResults AS (
    SELECT your_column,
           ROW_NUMBER() OVER (ORDER BY your_column DESC) AS RN
    FROM your_table
)
SELECT your_column
FROM SortedResults
WHERE RN = 1


This naturally sorts the rows in descending order and plumbed the depth with ease, eliminating the need for string reversal.

Performance: riding the SQLion

In the SQL kingdom, performance is king. Design your UDFs for speed when dealing with large data sets. SQL Server has you covered with indexes and efficient query structures.