DECODE statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: DECODE statement

  1. #1
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52

    Unhappy

    Hello all,
    I am trying to fetch some data from a column and run the substr operation on it, then decode the substr into something else. I tried something along these lines, but it did not work:

    select decode(substr(column_name,1,10) , 'DUCK') from tablex where column_name = 'EDGEWATER POND';

    This probably should be easy but it just isn't coming to me. I want to select a string (substring rather), and change it's value everywhere in this column. In this case I am trying to find all values called 'EDGEWATER POND', then decode the 'EDGEWATER' portion and change the value to 'DUCK'. I know the select isn't going to update anything, but I wanted to see if I could get the syntax working correctly before attempting to do the update.

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    UPDATE TABLEX SET COLUMN_NAME = REPLACE(COLUMN_NAME,'EDGEWATER','DUCK') WHERE COLUMN_NAME = 'EDGEWATER POND';

  3. #3
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52

    Thanks!

    That did the trick! I was going about it all wrong, or at least making it more complicated.

  4. #4
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    hi
    kmesser,
    i may be wrong but let me ask u why u used the REPLACE function if only u want to update all the rows with 'DUCK' where it is 'EDGEWATER POND'.
    u can also use the simple update statement like..

    update tablex set column_name='DUCK' where
    column_name='EDGEWATER POND';

    pls tell me if u have any other thing in mind....

    thanks
    pras

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    pras,

    He doesn't want to replace 'EDGEWATER POND' with 'DUCK'. He wants to replace 'EDGEWATER POND' with 'DUCK POND'. I could have said:

    update tablex set column_name = 'DUCK POND' where column_name = 'EDGEWATER POND';

    I assumed (dangerous, I know) that this might only be one of many such substring updates which grrickar is performing, so I decided to use REPLACE to display that functionality.

    Good point though. If you know exactly how the initial string and final string will look, better to hard code it than use a function.

Posting Permissions

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



Click Here to Expand Forum to Full Width