Extract only the filename from a file path

  • 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

  • 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
    😎

  • 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

  • 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

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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".

  • 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

  • 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

  • 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;

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply