In scenarios where there are multiple occurrences of a substring or delimiter, extracting the string from the position of its last occurrence can be tricky.
Below we are going to discuss a simple approach to solve this problem.
Example 1 - Extracting file extension from filename.
For ex - Filename.ext should return ext
Understanding the approach -
To find the position of the '.' before the extension, we can use the CHARINDEX function.
To make sure, it is the last occurrence, we are going to reverse the string using the REVERSE function.
This will being the last '.' to the beginning of the reversed string.
The CHARINDEX function will then be able to identify the position of the '.' which will give us the number of characters following the '.' in the original string (including the .).
SO, for our example, REVERSED string will be
txe.emaneliF.
The position of '.' in above string is 4 which tells us there are (4 - 1) = 3 charcters following the lasr '.' in the original string.
So, if we extract last 3 characters from the original string, we can get the extension.
To extract the last characters which are also the rightmost characters, we can use the RIGHT function.
The entire SQL would look like this -
-- Extract file name extension
declare @FN varchar(100) = 'Filename.ext';
--Select REVERSE(@FN)
Select RIGHT(@FN, CharIndex('.',REVERSE(@FN)) - 1)
Example 2 - Extracting filename from physical path.
For ex - 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\master.mdf'
We need to extract filename - master.mdf
Similar approach as above will give us the desired result.
Please see SQL below -
-- Extract filename from physical path
declare @FN varchar(100) =
'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\master.mdf'
Select RIGHT(@FN,CHARINDEX('\',REVERSE(@FN)) - 1)
--Select RIGHT(@FN, CharIndex('\',REVERSE(@FN)) - 1)