sql server script get the file name from path

 

created the below function to make it handy to get the file name from a string contains full file path.

select dbo.fn_lastpart  ('c:\program files\mssql\mypath\myfile.txt','\')

myfile.txt

create function dbo.fn_lastpart (@vString varchar(500) , @vSep varchar(9))         returns varchar(500)         as         begin         declare @stAmp int         set @stAmp =len(@vString )-charindex(@vSep,reverse(@vString))+1 -- to get the last  value after charecter         return SUBSTRING(@vString,@stAmp+1,LEN(@vString)) -- to get the filename only                             end