asked on

Oracle query to replace a substring

I have an oracle 11g table TB_Paths with a field pathname
I need to replace the path name as
replace all the \\KWI_Proj\ to \\kindle.com\proj\

keeping in mind that there can be following
\\KWI_Proj\123\Myproj
\\Kwi_Proj\125\Myproject
\\kwi_Proj\325\MYPROJ

in all the different cases of \\KWI_Pro it need to convert to \\kindle.com\proj\ ( without changing cases of the rest of the path

Peter Chan

Try

select replace('\\KWI_Proj\123\Myproj','\\KWI_Proj\','\\kindle.com\proj\') from dual;

Open in new window

Norie

Sam

You could use CONCAT and SUBSTR.

SELECT Pathname, CONCAT('\\kindle.com\proj\', SUBSTR(PathName, length('\\KWI_Proj\'))) AS New_PathName
FROM TB_Paths

Alex [***Alex140181***]

update TB_Paths
   set pathname = replace(upper(pathname), '\\KWI_PROJ\', '\\kindle.com\proj\');
--or
update TB_Paths
   set pathname = regexp_replace(pathname, '\\KWI_PROJ\', '\\kindle.com\proj\', 1, 1, 'i');

Open in new window

Depending on the amount of data to be updates, the regexp function may take a second more ;-)

Btw: if these paths are Windows based, the case does NOT matter at all!

Sam OZ

ASKER

hi Alex. I tried the sql. but it will convert pathname to uppercase
path is case sensitive as used in xml and used for creating uid

Norie

Here's my version as an actual UPDATE query.

UPDATE TB_Paths
SET pathname = CONCAT('\\kindle.com\proj\', SUBSTR(PathName, length('\\KWI_Proj\')))

Alex [***Alex140181***]