update column 2

[GROKING]

I have a column called NAMES with data that looks like this:
google
google - content
google
google - content


I need to run an update to remove the ' - content' from the column.

here is what I have so far.
UPDATE TABLE
SET COLUMN NAMES = DECODE(NAMES,' - CONTENT', '')
WHERE NAMES LIKE '%CONTENT';

Is this the best function to use to remove the - content from each row??

Thanks for the help!!

 

[BJCooperIT]

The DECODE will not do what you want. Try:

UPDATE TABLE
SET    NAMES = SUBSTR(NAMES,1,LENGTH(NAMES) - 10)
WHERE  NAMES LIKE '% - CONTENT';

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Beilstwh]

Just an FYI,
If you want to remove " - content" if it is anywhere in the string, you could do the following

UPDATE TABLE
SET NAMES = replace(NAMES,' - content')
WHERE NAMES LIKE '% - content%';

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

Here is a slight brain teaser: what code would you use to remove ' - content', regardless of the case of its characters, and still regardless of its position in the NAMES column?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

Assuming that there is only one "- content" in the string, then

 select DECODE(INSTR(UPPER(NAMES),' - CONTENT'),0,NAMES,
        DECODE(INSTR(UPPER(NAMES),' - CONTENT'),1,
               SUBSTR(NAMES,INSTR(UPPER(NAMES),' - CONTENT')+10),
               SUBSTR(NAMES,1,INSTR(UPPER(NAMES),' - CONTENT')-1)||
               SUBSTR(NAMES,INSTR(UPPER(NAMES),' - CONTENT')+10))) NAMES
 FROM TABLE;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Great work, Bill, but Oracle doesn't allow one to make this one look "pretty", do they?

Have a star for all of the work that you've put in on this thread today.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

Thanks for the star. It is always nice to get a star from someone as good as you. Absolutely no sarcasm intended.

Bill
Oracle DBA/Developer
New York State, USA

 

[djbjr]

Wouldnt that be starcasm?