Oracle Regular Expressions - Replace all occurrence of string matching pattern

Question

Using Oracle regular expression particularly regexp_substr and replace functions, I'm trying to find a way to replace strings that matches the pattern. To explain better, here's a sample data.

<p>Hello $(KNOWN_AS),</p>

<p>Today is your lucky day. Being your birthday on $(DATE_OF_BIRTH), you have just won $1,000,000.00. To claim, go to https://www.example.com/$(KNOWN_AS),</p>

<p>Regards,<br />
Administrator</p>

What I want to do is replace all the strings matching the $(?) with <xsl:value-of select="?"/>. I have tried the following but it only replaces the first occurrence.

with t1 as ( select '<p>Hello $(KNOWN_AS),</p> <p>Today is your lucky day. Being your birthday on $(DATE_OF_BIRTH), you have just won $1,000,000.00. To claim, go to https://www.example.com/$(KNOWN_AS),</p> <p>Regards,<br /> Administrator</p>' h
              from dual
)
select replace ( h, r, replace(replace( r,'$(','<xsl:value-of select="' ),')','"/>') ) n
  from ( select h, regexp_substr( h, '\$\((.*?)\)') r
          from t1 )

Result:

<p>Hello <xsl:value-of select="KNOWN_AS"/>,</p> <p>Today is your lucky day. Being your birthday on $(DATE_OF_BIRTH), you have just won $1,000,000.00. To claim, go to https://www.example.com/<xsl:value-of select="KNOWN_AS"/>,</p> <p>Regards,<br /> Administrator</p>

Appreciate any suggestions on how to do this properly.

Wiktor Stribiżew · Accepted Answer · 2022-09-08 11:47:41Z

1

You can simply use regexp_replace:

select regexp_replace(h, '\$\(([^()]*)\)', '<xsl:value-of select="\1"/>') as N from t1

See the DB fiddle.

Details:

\$\( - $( string
([^()]*) - Group 1 (\1 refers to this group value): any zero or more chars other than ( and )
\) - a ) char.

answered Sep 8, 2022 at 11:47

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Regular Expressions - Replace all occurrence of string matching pattern

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related