Post reply

Extract filename from fully qualified path

  • November 14, 2007 at 11:00 am

    #179338

    I have a varchar(255) column that contains the fully qualified path for a file.

    For example:

    C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyFile.txt

    I need to break this data into the path, filename and extension.

    @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'

    @Filename = 'MyFile.txt'

    @Extension = 'txt'

    I don't care if the path ends with a backslash or not.

    Nor the filename containing the extension or not.

    Nor the extension beginning with a period or not.

    I can always strip/add them as necessary.

    I'm thinking I need a loop to find the last backslash? Or some trick using REVERSE to find it? Any other ideas?

    I'm not asking for code, just a point in the right direction in case I missed the obvious. Or some secret undocumented system stored procedure.

    Thank you in advance for any assistance you can provide.

    Joe

  • November 14, 2007 at 11:11 am

    #751196

    yeah a double reverse will work here; i broke it into a couple of steps to make it a bit more obvious:

    declare @filename varchar(1000)

    set @filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyFile.txt'

    select

    substring(reverse(@filename),1,charindex('\',reverse(@filename)) ), --txt.eliFyM\

    reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ), --\MyFile.txt

    reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) -1 ) ), --MyFile.txt,

    --returns: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\

    substring(@filename,1,charindex(reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ),@filename) )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 14, 2007 at 2:19 pm

    #751266

    Thats pretty slick Lowell. I did not know about REVERSE. I also got

    SELECT SUBSTRING(@filename,1,LEN(@filename)-(CHARINDEX('\',REVERSE(@filename))-1)) AS Path

    to work for path. I guess you learn something new everyday. Thanks for the lesson.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 14, 2007 at 3:17 pm

    #751290

    Thanks Lowell and Greg.

    I really appreciate the quick feedback and sample code!

    Sometimes I go down tangents and write a whole bunch of code only to find out that there is an undocumented system stored procedure or a simple technique.

    This time I thought I'd check with the experts first.

    Thanks again,

    Joe

  • November 14, 2007 at 3:57 pm

    #751299

    Joe Barbian (11/14/2007)

    This time I thought I'd check with the experts first.

    Thanks again,

    Joe

    Joe

    I just want to make one thing perfectly clear. I am not to be included with the experts :). My knowledge of SQL is only the tip of the iceberg of what is there to know. I just enjoy working on others problems where I can, because it is the best opportunity I have to learn new stuff.

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 14, 2007 at 6:45 pm

    #751329

    Greg Snidow (11/14/2007)

    I just want to make one thing perfectly clear. I am not to be included with the experts :). My knowledge of SQL is only the tip of the iceberg of what is there to know. I just enjoy working on others problems where I can, because it is the best opportunity I have to learn new stuff.

    With as much as SQL Server has expanded, I don't think anyone can be considered an expert on SQL Server as a whole any longer. 🙂

    K. Brian Kelley
    @kbriankelley

  • September 28, 2015 at 3:34 am

    #1829817

    SELECT RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-1 )) AS NameOfFile

  • September 28, 2015 at 3:52 am

    #1829820

    Path:

    SELECT substring(filename,1,charindex(reverse(substring(reverse(filename),1,charindex('\',reverse(filename)) ) ),filename) )

    FileName:

    SELECT RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-1 )) AS NameOfFile FROM SSIS_LogFileNameRecord

  • September 28, 2015 at 4:51 am

    #1829826

    Anil Kolla (9/28/2015)

    Path:

    SELECT substring(filename,1,charindex(reverse(substring(reverse(filename),1,charindex('\',reverse(filename)) ) ),filename) )

    FileName:

    SELECT RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-1 )) AS NameOfFile FROM SSIS_LogFileNameRecord

    Good effort, though it did take eight years longer than Lowell :hehe:

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 28, 2015 at 12:12 pm

    #1829971

    Anil Kolla (9/28/2015)

    SELECT RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-1 )) AS NameOfFile

    'Zactly.... no expensive double reverse required. One is bad enough. 😀 No length calculation required, either.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 17, 2017 at 7:55 am

    #1955652

    Query is almost correct.
    I corrected and now it works for me

    RIGHT([physical_device_name],CHARINDEX('\',REVERSE([physical_device_name]))-1) AS NameOfFile

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy