Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    17

    Unanswered: SQL Server update help

    I have a column in a table that stores the full pathname for files, ie. C:\folder1\folder2\folder3\myfile.htm
    What I want to do is to update all these pathnames to just be the filename only, ie. myfile.htm
    The pathnames could be different levels of subfolders before the filename. So what I really need is keep everything after the last "\" in the stored path. Is there a way to do this in an update query? Thanks.
    Last edited by nbrege; 08-23-13 at 15:01.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    If all the files end with an extension then I'd think one approach is to use a patindex function along with a right function and a substring.
    Last edited by corncrowe; 08-23-13 at 15:36.

  3. #3
    Join Date
    Mar 2010
    Posts
    17
    I'm not familiar with that ... can you post a quick example?

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try looking up BOL for examples of using char or pat index functions.

  5. #5
    Join Date
    Mar 2010
    Posts
    17
    what is BOL?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: misread the question... code updated
    Code:
    SELECT full_path
         , Reverse(full_path) As reverse_it
         , CharIndex('\', Reverse(full_path)) As last_slash
         , Len(full_path) As string_length
                             /* string_length  - last_period                        + 2 */
         , SubString(full_path, Len(full_path) - CharIndex('\', Reverse(full_path)) + 2, 1000) As filename
    FROM   (SELECT 'C:\folder1\folder2\folder3\myfile.htm' As full_path) As x
    Last edited by gvee; 08-23-13 at 15:50. Reason: Misread question
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by nbrege View Post
    what is BOL?
    Wait long enough and someone will give you the answer.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by nbrege View Post
    what is BOL?
    BOL = Books OnLine

    Books Online for SQL Server 2012
    George
    Home | Blog

Posting Permissions

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