Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Unanswered: Update part of a column only

    Newbie here.

    How would I do the following update?

    I have over 100,000 rows and want to knock the first two characters off one of the columns in each row.

    i.e. currently have

    \pcdocs\various\various

    and want this to read

    \docs\various\various

    I just need to remove the first two characters "pc".
    Last edited by Leesifer; 09-18-04 at 15:08.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Maybe something like this:

    update tbl
    set fld = case when datalength(fld) > 8 then
    case substring(fld, 1, 8) when '\pcdocs\' then '\docs\' + substring(fld, 9, datalength(fld)-8) end end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Leesifer, there are probably a dozen different ways to do this, and which is best depends on what your data looks like and EXACTLY what operation you want to do. For instance, do you just want to replace "\pcdocs\" with "\docs\", or might some of your data look like "\pcfiles\" or even "\abdocs\".

    You need to familiarize yourself with the large number of character manipulation functions available within TSQL. After that, coding is pretty much like building with LEGO blocks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Posts
    4
    Quote Originally Posted by blindman
    Leesifer, there are probably a dozen different ways to do this, and which is best depends on what your data looks like and EXACTLY what operation you want to do. For instance, do you just want to replace "\pcdocs\" with "\docs\",
    That is EXACTLY what I want to do, blindman.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov's solution is fine for that. Simpler in syntax is:

    REPLACE (fld , '/pcdocs/' , '/docs/')

    ...but this has the drawback of hosing things up if you have a lower-level subdirectory name '..../pcdocs/....' as well, and you don't want to alter it.

    Again, any time you spend reviewing the two dozen or so built-in character functions will be richly rewarded by saving you coding time later on.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Posts
    4

    Smile

    Thanks for your help.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Back up the table first, BEFORE you do anything...

    SELECT * INTO yourBackup FROM yourTable

    Just in Case
    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
  •