ORACLE SQL: Replace part of text field ignoring case

Question

I need to replace a path stored in an Oracle DB text field. However paths have been specified with different cases (ie MYPATH, MyPath, Mypath, mypath). When using a combination of REPLACE and UPPER, it does not work as I need it to, ie:

UPDATE Actions 
SET Destination = REPLACE(UPPER(Destination), 'MYPATH', 'My_New_Path')

This does the replace but leaves everything in upper case - even for the rows where there is nothing to replace

BEFORE: MyPath\FileName - AFTER: My_New_Path\FILENAME
BEFORE: DummyText - AFTER: DUMMYTEXT

What I really need is to replace any occurrence of MyPath, regardless of the case to My_New_Path, without touching the casing on other rows or other part of the field

Any ideas? I have been scratching my head to no avail...

PS: Working with Oracle 9...

Answer 1

Perhaps this:

UPDATE Actions
SET Destination = SUBSTR(Destination,1,INSTR(UPPER(Destination), 'MYPATH')-1)
                  || 'My_New_Path'
                  || SUBSTR(Destination,INSTR(UPPER(Destination), 'MYPATH')+6)
WHERE UPPER(Destination) LIKE '%MYPATH%';

Answer 2

I was going to suggest using regexp_replace() but IIRC that's not available in Oracle 9.

Something like this then:

UPDATE atable
SET afield=SUBSTR(afield, 1, (INSTR(UPPER(afield),'old')-1) 
   || 'new' 
   || SUBSTR(afield, INSTR(UPPER(afield),'old')+LENGHT('old'), LENGTH(afield))
WHERE afield LIKE '%' || 'old' || '%';

Answer 3

A combination + tweaking of the 2 answers made it work:

UPDATE actions SET Destination=SUBSTR(Destination, 1, (INSTR(UPPER(Destination),'OLD')-1))
|| 'NEW' || SUBSTR(Destination, INSTR(UPPER(Destination),'OLD')+LENGTH('OLD'), LENGTH(Destination)) WHERE UPPER(Destination) LIKE '%OLD%';

Thanks guys! - I would vote both answers up but I just signed up and don't have enough reputation...