CHARINDEX provides an easy way to search for the first occurrence of a string in another string. By reversing
the order of the string it can also provide a straightforward way of finding the last occurrence of a string.
For instance if a string contains the full path to a file, such as :
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf
This technique can be used to extract just the filename (i.e. the part after the last '\') :
By searching for the location of the last occurence of the delimiter '\' we can easily strip off
just the filename using the RIGHT
The following SQL does just that :
DECLARE @FullPath VARCHAR(200)
SET @FullPath =
'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'
SELECT RIGHT(@FullPath , CHARINDEX
If we run this query it returns just the filename.
is searching the reversed string to find the
position of the last '\' character.
function is then used to extract all characters to the
right of this point.
The following query uses this technique to extract the file name from the full path name
in the sys.master_files
system view :