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