Hi Tom!I have a string like this format - "xxx/yyy/zzz"
I want replace words using these rules:
if we meet "xxx" then change at "x1"
if we meet "yyy" then chage at "y1"
if we meet "zzz" then chage at "z1"
And then end I want to get string like this - "x1/y1/z1".
My first variant is:
With Example(str) as
(select 'xxx/yyy/zzz' from dual)
select
LISTAGG( Case RegExp_SubStr(str,'[^/]+',1,level)
When 'xxx' then 'x1'
When 'yyy' then 'y1'
When 'zzz' then 'z1'
End,
'/') as NewString
from Example
connect by RegExp_SubStr(str,'[^/]+',1,level) is not null
It works! But so long!
I want to make change at one time using RegExp_Replace.
But I don't know how to do it!
Want to use backreference like this:
select
RegExp_Replace(str, '(xxx|yyy|zzz)',
Case '\1'
When 'xxx' then 'x1'
When 'yyy' then 'y1'
When 'zzz' then 'z1'
End
) as NewString
from Example
But it doesn't work. Result is "//"
Can you explain why? I can not using backreference at this examle?
You can use backreferences in case expressions like that.
Instead, match x, y or z once, followed by two identical matches with a regex like:
(x|y|z)\1\1
Then use a backreference in the replacement string followed by 1:
\11
Note this assumes you will have exactly three matching characters; if there can be more the results may not be what you expect:
create table t ( str varchar2(30) );
insert into t values ( 'xxx/yyy/zzz' );
insert into t values ( 'xx/yyyy/zzzzz' );
select str, regexp_replace(
str, '(x|y|z)\1\1', '\11'
) as NewString
from t;
STR NEWSTRING
xxx/yyy/zzz x1/y1/z1
xx/yyyy/zzzzz xx/y1y/z1zz