The ProblemToday 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.