regexoracle-databasereplaceregexp-substr

Oracle Regular Expressions - Replace all occurrence of string matching pattern

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.

Solution

  • You can simply use regexp_replace:

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

    See the DB fiddle.

    Details: