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

Keywords

SQL,scripts,TSQL


Comments
Post by slava on Wed 21 Feb 2018 11:16. Report Inappropriate Post

have error:
Msg 536, Level 16, State 4, Line 2
Invalid length parameter passed to the RIGHT function.
Warning: Null value is eliminated by an aggregate or other SET operation.
Post by Gordo on Wed 19 Jun 2019 21:02. Report Inappropriate Post

"Invalid length parameter passed to the RIGHT function." what you are searching for does not exist in the string you are searching. Don't forget to reverse the search string and also check that it exists beforehand.
Post by Md.Ibrahim Khalil on Tue 31 Aug 2021 10:57. Report Inappropriate Post

thanks, its working fine
Post by Jeffrey Cuen on Mon 18 Apr 2022 17:25. Report Inappropriate Post

What if you what to SELECT RIGHT but from finding the 3rd "\", to get the result \MSSQL\DATA\AdventureWorks_Data.mdf'

@FullPath ='C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'

Note: want to select by this Charater "\" when appears on 3rd occurence.
Post by Satish K on Thu 22 Jun 2023 12:28. Report Inappropriate Post

What if you what to return only "MSSQL" from:

@FullPath ='C:\Program Files\Microsoft SQL Server\MSSQL\DATA\AdventureWorks_Data.mdf'

Post a comment   No login required !

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