Coder Tidbit: SQL - Last index of specific character

The Problem

Today I had the opportunity to work with data pertaining to files on a network share and the feature I was working on was to include this information in a site search. To match the needs of the search I needed to include the extension of the file in the results. However, the only information that I had was the full path to the file, where it resided on the network share.

In C# I could have done something like .LastIndexOf(x) but I did not know of any SQL equivalent so I sought after a solution and came up with the following, after some Googling.

The Pattern Used

What's Going On

SUBSTRING(param1, param2, param3)
param1 is field in which we're substringing
param2 is the starting position to begin the getting the substring
param3 is the distance across the char array the substring function should travel before returning

To get the starting position we can get the last '.' and substring to the end of the string. To get the 'last index' of the '.' we can reverse the string with REVERSE(...) and then use that in conjunction with CHARINDEX(...). If my path is \\testpath\TestFile.txt the result from using this is 4 since the string is reversed it's still counting up and since '.' is at the fourth position of the character array. With this information we know that we can then subtract this number from the length of string with LEN(...) to get the position of the '.' we're after. To get the return format of .txt we need to add 1 to the start position of the SUBSTRING call.

Now, we have our beginning position for our substring and also the length needed for our extension, otherwise everything from the '.' to the end of the string and we now have our extension.