DECODE statement

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.

UPDATE TABLEX SET COLUMN_NAME = REPLACE(COLUMN_NAME,'EDGEWATER','DUCK') WHERE COLUMN_NAME = 'EDGEWATER POND';

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

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

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.