Extract only the filename from a file path

• kevaburg

  SSCoach

  Points: 18131

  More actions

  April 13, 2017 at 8:35 am

  #322961

  Hi Folks,

  is this really the best way to extract the file name from a file path?

  
select replace( 
reverse(
left(
reverse(physical_name)
 , charindex('\', reverse(Physical_Name))
 )
 )
 , '\', ''
 ) as FileName
  
 from sys.master_files
 order by 1 asc;


  Sorry for the formatting.....somehow it didn't come out as I wanted... 🙁

  Regards,
  Kev

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  April 13, 2017 at 8:38 am

  #1938067

  kevaburg - Thursday, April 13, 2017 8:35 AM

  Hi Folks,

  is this really the best way to extract the file name from a file path?

  
select replace( 
reverse(
left(
reverse(physical_name)
 , charindex('\', reverse(Physical_Name))
 )
 )
 , '\', ''
 ) as FileName
  
 from sys.master_files
 order by 1 asc;


  Sorry for the formatting.....somehow it didn't come out as I wanted... 🙁

  Regards,
  Kev

  You could also skip the replace and do -1 after the charindex but this method is pretty good
  😎

• Thom A

  SSC Guru

  Points: 99130

  More actions

  April 13, 2017 at 8:43 am

  #1938073

  Personally, I feel this might be a bit more concise:
  DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

  SELECT @FilePath AS FilePath,
       RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) -1) AS FileName;
  No need to REVERSE, LEFT, REVERSE then.
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• kevaburg

  SSCoach

  Points: 18131

  More actions

  April 13, 2017 at 8:45 am

  #1938074

  Eirikur Eiriksson - Thursday, April 13, 2017 8:38 AM

  kevaburg - Thursday, April 13, 2017 8:35 AM

  Hi Folks,

  is this really the best way to extract the file name from a file path?

  
select replace( 
reverse(
left(
reverse(physical_name)
 , charindex('\', reverse(Physical_Name))
 )
 )
 , '\', ''
 ) as FileName
  
 from sys.master_files
 order by 1 asc;


  Sorry for the formatting.....somehow it didn't come out as I wanted... 🙁

  Regards,
  Kev

  You could also skip the replace and do -1 after the charindex but this method is pretty good
  😎

  Hi there Eirikur,

  thanks for the comment.  I really thought there might be a better and easier way to do it....this took me an age to work out!

  Now I remember why I have avoided development for so Long... rotfl

• kevaburg

  SSCoach

  Points: 18131

  More actions

  April 13, 2017 at 8:47 am

  #1938075

  Thom A - Thursday, April 13, 2017 8:43 AM

  Personally, I feel this might be a bit more concise:
  DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

  SELECT @FilePath AS FilePath,
       RIGHT(@FilePath, CHARINDEX('\', REVERSE(@FilePath)) -1) AS FileName;
  No need to REVERSE, LEFT, REVERSE then.
  

  It is more readable as well I think.....cheers Thom

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  April 13, 2017 at 10:45 am

  #1938099

  If you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial

  DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

  SELECT TOP 1
   FileName = split.Item
FROM  dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  April 13, 2017 at 1:27 pm

  #1938118

  I like to code to handle the case where there's no '\' in the data, to avoid the dreaded "Invalid length passed to RIGHT function."

  
DECLARE @FilePath VARCHAR(500) = 'My File Name.pdf';
SELECT @FilePath AS FilePath,  
   RIGHT(@FilePath, CHARINDEX('\', REVERSE('\' + @FilePath)) -1) AS FileName;

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• kevaburg

  SSCoach

  Points: 18131

  More actions

  April 27, 2017 at 12:44 am

  #1940141

  Phil Parkin - Thursday, April 13, 2017 10:45 AM

  If you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial

  DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

  SELECT TOP 1
   FileName = split.Item
FROM  dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;

  Hi Phil,
  what is the DelimitedSplit8k?  That is really new to me....

  Regards,
  Kev

• Thom A

  SSC Guru

  Points: 99130

  More actions

  April 27, 2017 at 1:51 am

  #1940147

  kevaburg - Thursday, April 27, 2017 12:44 AM

  Phil Parkin - Thursday, April 13, 2017 10:45 AM

  If you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial

  DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

  SELECT TOP 1
   FileName = split.Item
FROM  dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;

  Hi Phil,
  what is the DelimitedSplit8k?  That is really new to me....

  Regards,
  Kev

  http://www.sqlservercentral.com/articles/Tally+Table/72993/ 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• hemant.bamane

  SSC Enthusiast

  Points: 107

  More actions

  April 27, 2017 at 8:11 am

  #1940212

  SELECT
  --SUBSTRING(orignal file name,Last Char Index Of '\', FileName Length)
  SUBSTRING(    physical_Name,(LEN(physical_Name)-CHARINDEX('\',REVERSE(physical_Name))+2),LEN(physical_Name))
  from sys.master_files
  order by 1 asc;