T-SQL - Stripping Path From FileName-VBForums
Results 1 to 3 of 3

Thread: T-SQL - Stripping Path From FileName

  1. #1

    Thread Starter
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,285

    T-SQL - Stripping Path From FileName

    If you have a field in your database that contains the fille path to a file, such as \\Server\SharePoint\Folder1\SubFolder1\SomeOtherFolder\FileName.EXT and have had the need to return just FileName.EXT should find this handy.
    In the select statement, just use this:
    Code:
    RIGHT(fldFileName, CHARINDEX('\', REVERSE(fldFileName))-1)
    Replacing fldFileName with your own field.
    It works like this:
    REVERSE(fldFileName))
    reverses the string so that it is backwards
    CHARINDEX('\', REVERSE(fldFileName))
    takes that reversed string, and finds the first '\'.... wich will be the LAST '\' when normal. Take one off of that (because we don't want the '\', and use that to get the RIGHT characters of the "proper" string.

    It seems kind of wierd, but it does work. Far easier than parsing it out in VB or even in SQL.

    TG
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  2. #2
    New Member
    Join Date
    Sep 2012
    Posts
    1

    Re: T-SQL - Stripping Path From FileName

    Simple, yet brilliant. Thanks, this helped tremendously.

  3. #3
    New Member
    Join Date
    Nov 2012
    Posts
    4

    Re: T-SQL - Stripping Path From FileName

    cool tip - I made a slight change:

    RIGHT('\'+fldFileName, CHARINDEX('\', REVERSE('\'+fldFileName))-1)

    This way it will always work whether the '\' is in the filename or not (the original errors if it isn't)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.