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 '\') :
AdventureWorks_Data.mdf
By searching for the location of the last occurrence of the delimiter '\' we can easily strip off
just the filename using the
RIGHT function.
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
('\'
,REVERSE(@FullPath))-1)
If we run this query it returns just the filename.
Here
CHARINDEX is searching the reversed string to find the
position of the last '\' character.
The
RIGHT 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 :
SELECT physical_name
,RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) file_name
FROM sys.master_files