Skip to Main Content
  • Questions
  • To make replace substrings at one time

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kirim.

Asked: October 15, 2024 - 8:41 pm UTC

Last updated: October 21, 2024 - 12:59 pm UTC

Version: Oracle 19c

Viewed 1000+ times

You Asked

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?

and Chris said...

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   

Rating

  (2 ratings)

Comments

A reader, October 16, 2024 - 10:13 pm UTC

Hello Chris !

For string 'xxx/yyy/zzz' you answer is correct.
But it was just an simple example. In real example there are different values and length substrings.

Example - "Car/Window/Play/Car"
We need to use change like these: Car = 1, Play = 3

After replcae result should be "1/Window/3/1"

With Example(str) as 
 (select 'Car/Window/Play/Car' from dual)

select RegExp_Replace(str,'([^/]+)', Case '\1' When 'Car' then '1' When 'Play' then '3'  End ) as ResultStr
   from Example



Result is "///".
CASE expression is not work here.

And about using function in argument "replace_string ".
If I use function UPPER it is applied only to not backreferences part:

With Example(str) as 
 (select 'Car/Window/Play/Car' from dual)

select RegExp_Replace(str,'([^/]+)', Upper('\1_s')) as ResultStr
   from Example


Result is "Car_S/Window_S/Play_S/Car_S"

Why?



Chris Saxon
October 17, 2024 - 10:24 am UTC

Like I said - you can't use a case expression like this! It's not looking for the back-reference, it's checking the literal value "\1".

If you want to replace Car with 1 & Play with 3, why not just use replace? Why do you want to use a regex for this?

I feel like there's lots of background info still missing here.

Even more better..

Rajeshwaran, Jeyabal, October 18, 2024 - 3:56 pm UTC

.....
Instead, match x, y or z once, followed by two identical matches with a regex like:
.......


Or even more better, take each character and check it got repeated collocated, if so perform the replacement - sort of like this

demo@PDB1> select str, regexp_replace( str, '(.)\1+','\11') new_str from t;

STR                            NEW_STR
------------------------------ ------------------------------
xxx/yyy/zzz                    x1/y1/z1
xx/yyyy/zzzzz                  x1/y1/z1
aaa/bbbb/ccccc                 a1/b1/c1

Chris Saxon
October 21, 2024 - 12:59 pm UTC

Based on the review above, I don't think that's what the OP wants.