Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Question Unanswered: replacing paths in a filename collumn

    [[Disclaimer: I am by no means a highly-skilled DBA. I have just enough db skills to get my job done, which involves light sql server and oracle tasks. I appreciate the help, guys.]]

    I am trying to replace the path section of filenames listed in a collumn of my database. The filenames are UNC paths, and all files have 8 digit filenames with the same extension (they are all tif images). The length of the paths in the filenames will change, however.

    Here is what my old paths look like:

    \\image\imagesN\YY-MM\ABCDEFGH.TIF

    ..and here is the new path (all of the images are being consolidated into one directory):

    \\als-image\alscom31\imagesdb\ABCDEFGH.TIF

    -------------------------------------------------------------
    I have tried using the following query to do this:

    update image set filename=replace(filename,'\\image\images5\00-08','\\als-image\alscom31\imagesdb')

    This returns insanely crazy results like 460239 row affected, when it should be about a thousand.
    --------------------------------------------
    I have also tried the following:

    update image set filename=right(filename,12) + '\\als-image\alscom31\imagesdb' where left(filename,21) = '\\image\images5\00-08'

    This affects zero rows.

    Please guide me down the one true path. My coworkers are clueless and I'm quickly running out of disk on the old box.

    peace,

    -jake
    c o g i t o e r g o s u m

  2. #2
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: replacing paths in a filename collumn

    How many years?

    How many N's in imageN?

    Originally posted by jake.b
    [[Disclaimer: I am by no means a highly-skilled DBA. I have just enough db skills to get my job done, which involves light sql server and oracle tasks. I appreciate the help, guys.]]

    I am trying to replace the path section of filenames listed in a collumn of my database. The filenames are UNC paths, and all files have 8 digit filenames with the same extension (they are all tif images). The length of the paths in the filenames will change, however.

    Here is what my old paths look like:

    \\image\imagesN\YY-MM\ABCDEFGH.TIF

    ..and here is the new path (all of the images are being consolidated into one directory):

    \\als-image\alscom31\imagesdb\ABCDEFGH.TIF

    -------------------------------------------------------------
    I have tried using the following query to do this:

    update image set filename=replace(filename,'\\image\images5\00-08','\\als-image\alscom31\imagesdb')

    This returns insanely crazy results like 460239 row affected, when it should be about a thousand.
    --------------------------------------------
    I have also tried the following:

    update image set filename=right(filename,12) + '\\als-image\alscom31\imagesdb' where left(filename,21) = '\\image\images5\00-08'

    This affects zero rows.

    Please guide me down the one true path. My coworkers are clueless and I'm quickly running out of disk on the old box.

    peace,

    -jake

  3. #3
    Join Date
    Jan 2004
    Posts
    10
    Three years, and really only two 'N's

    I am doing it one folder at a time because the sheer size of each folder makes it prohibitive to do it all in one swoop.
    c o g i t o e r g o s u m

  4. #4
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    depending

    What is the field type. TEXT and NTEXT cannot use the REPLACE function.

    Have you tried to do it in portions

    (i.e.

    update image set filename=replace(filename,'images1','alscom31')
    update image set filename=replace(filename,'images2','alscom31')
    update image set filename=replace(filename,'images3','alscom31')
    update image set filename=replace(filename,'images4','alscom31')
    update image set filename=replace(filename,'images5','alscom31')

    update image set filename=replace(filename,'images','als-image')

    ...

    simple but it should work.

    I may be offbase but I feel that it the function is treating \\ as an escape character.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't seem to have a problem (well at least not with this SQL)

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (col1 varchar(8000))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT '\\image\imagesN\02-01\File1.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File2.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File3.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File4.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File5.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File6.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File7.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File8.tif' UNION ALL
    SELECT '\\image\imagesN\02-01\File9.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File1.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File2.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File3.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File4.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File5.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File6.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File7.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File8.tif' UNION ALL
    SELECT '\\image\imagesN\02-02\File9.tif'
    GO
    
    SELECT * FROM myTable99
    
    UPDATE myTable99 SET Col1 = REPLACE(Col1,'\\image\imagesN\02-01\','\\als-image\alscom31\imagesdb\')
    WHERE Col1 LIKE '\\image\imagesN\02-01\'+'%'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    10

    Re: depending

    Originally posted by SimSoph
    What is the field type. TEXT and NTEXT cannot use the REPLACE function.

    I may be offbase but I feel that it the function is treating \\ as an escape character.
    The datatype is shown as "varchar," and once again I'm kind of a newbie so I don't know whether or not that answers your first question.

    If it does treat the UNC identifier as escapes, then I see no way other than breaking down the 'replace' into smaller statements.

    That should theoretically work because both the old path and the new path are three directories deep.
    c o g i t o e r g o s u m

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Question

    Did you cut and paste my code in to QA to see what happens?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    10
    Originally posted by Brett Kaiser
    Did you cut and paste my code in to QA to see what happens?
    I'm doing that now. It's on another box, so It'll take about 3 minutes.
    c o g i t o e r g o s u m

  9. #9
    Join Date
    Jan 2004
    Posts
    10
    Originally posted by Brett Kaiser
    Did you cut and paste my code in to QA to see what happens?
    That method seems to work fine.. I'll try and apply that.

    Thanks!
    c o g i t o e r g o s u m

  10. #10
    Join Date
    Jan 2004
    Posts
    10
    This is what I'm going to try;
    ----------------------------------

    UPDATE image SET filename = REPLACE(filename,'\\image\images5\00-08','\\als-image\alscom31\imagesdb')
    WHERE filename LIKE '\\image\images5\00-08'+'%'
    GO
    c o g i t o e r g o s u m

  11. #11
    Join Date
    Jan 2004
    Posts
    10
    Originally posted by jake.b
    This is what I'm going to try;
    ----------------------------------

    UPDATE image SET filename = REPLACE(filename,'\\image\images5\00-08','\\als-image\alscom31\imagesdb')
    WHERE filename LIKE '\\image\images5\00-08'+'%'
    GO
    That query gives zero rows affected as well. Bummer.
    c o g i t o e r g o s u m

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK ...let's put this puppy to bed before I can start on my Margarittas


    What do yo uget when you run

    SELECT * FROM Image
    WHERE filename LIKE '\\image\images5\'+'%'

    If that returns nothing, post a couple sample rows from a plain select *...

    used [ code ] [ /code ] tags to retain formatting

    Just eliminate the space in the tags...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2004
    Posts
    10
    Nevermind. I got it working. THANKS!!
    c o g i t o e r g o s u m

  14. #14
    Join Date
    Jan 2004
    Posts
    10
    Originally posted by Brett Kaiser
    OK ...let's put this puppy to bed before I can start on my Margarittas


    What do yo uget when you run

    SELECT * FROM Image
    WHERE filename LIKE '\\image\images5\'+'%'

    If that returns nothing, post a couple sample rows from a plain select *...

    used [ code ] [ /code ] tags to retain formatting

    Just eliminate the space in the tags...

    The problem was that I had hastily picked a few folders which were no longer in the db, but the data it points to was still on the disk. It was a stupid mistake, but your method looks much better than the ones I had cooked up and it works great. Thanks for the help.. I think one of these days I'll be good at this stuff.
    c o g i t o e r g o s u m

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    GREAT!

    Maybe I'll up the clock...

    Code:
    DECLARE @Weekend datetime
     SELECT @Weekend = 'Jan 16 2004 16:30:00'
     SELECT DATEDIFF(mi, GetDate(), @Weekend)/60.00 As Hours_till_Margaritaville
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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