Searching a string for the last occurrence of a given string

Category : Tips User Rating : 4 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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 occurence 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
Link back to this article : http://www.sqlmatters.com/Articles/Searching a string for the last occurrence of a given string.aspx

Keywords

SQL,scripts,TSQL


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered