REVERSE – Implementing LastIndexOf in T-SQL

T-SQL is not ideal for doing string manipulation, but sometimes you just need to get something done quickly with the tool you’ve got. A lot of people that use T-SQL also have a programming background. So, if you ask them for a string method that can be used to find the last instance of a character in a string, they will respond with the LastIndexOf string method.

A real world example where you might need to use this would be looking at a FileName that includes the full path and returning only the file name. To accomplish this, you want to find and capture all of the characters after the last \. Here is a small table with sample data that contains enough variety to illustrate how to accomplish this:

DECLARE @FileNames TABLE (
 FileNameID int IDENTITY
 , FileName varchar(255))

INSERT INTO @FileNames (FileName)
VALUES ('C:\Temp\Baseball\WadeBoggs.jpg'),
 ('C:\Temp\Baseball\RogerClemens.jpg'),
 ('C:\Temp\Football\BarrySanders.jpg'),
 ('C:\Temp\Hockey\BasilMcRae.jpg')

For those of you that are just interested in the solution, this will do it:

SELECT RIGHT(FileName, CHARINDEX('\', REVERSE(FileName)) - 1) FROM @FileNames

For those of you that don’t just copy and paste code from the internet without understanding how it works, let’s break this down.

1. We REVERSE the FileName to flip the string and make the last whack the first.
2. Then we use CHARINDEX to find the first whack (of that flipped string).
3. Next we use the RIGHT function to grab every character beyond that position (subtracting 1 to not return the last backslash).

Although T-SQL isn’t my first choice for doing string manipulation, this just goes to show that with a little bit of creativity you can use some of the built in string functions to avoid rolling your own user defined function (many people would have written a function that would have used CHARINDEX to ‘walk forward’ through the string until it found the last backslash).