replace function in oracle

Hello Experts:

I two columns in ORA table with NAS path i.e.

\\folder1\folder2\folder3\some file name1.doc
\\folder1\folder2\folder3\some file name2.doc
\\folder1\folder2\folder3\some file name3.doc
\\folder1\folder2\folder3\some file name4.doc

Open in new window


I would like to replace it with new NAS path \\folder4\folder5\folder6\

output like this

 \\folder4\folder5\folder6\some file name1.doc
 \\folder4\folder5\folder6\some file name2.doc
 \\folder4\folder5\folder6\some file name3.doc
 \\folder4\folder5\folder6\some file name4.doc

Open in new window


Can somebody please assist with this update query?

Regards,
CS
LVL 5
CalmSoulAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can also use something like
update table
set col = replace(col,'\\folder1\folder2\folder3\','\\folder4\folder5\folder6\')
where col like = '\\folder1\folder2\folder3\%';

Open in new window

to replace all occurrences.
0
 
Walter RitzelSenior Software EngineerCommented:
update table
set column = replace(column,'\\folder1\folder2\folder3\','\\folder4\folder5\folder6\')
where key = < value>;

Open in new window

0
 
awking00Commented:
You could also accomplish the above without the replace function -
update yourtable
set path =  '\\folder4\folder5\folder6'||substr(path,instr(path,'\',-1))
where path like '\\folder1\folder2\folder3\%';

However, since you mention two columns, are you trying to replace the path in one column with the path from another column? If so, could you provide some sample data for both columns and your expected output?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.