CODEWITHSUNDEEP
sqloracle-databasereplaceoracle11g
user3138025
user3138025

Reputation: 825

SQL replace string by variable character position

I'm using Oracle 11.g. I have to replace a string within an eMail clob before I send it. The large string looks like:

<p>
Text right before the HP image.</p>
<p>
<img alt="" src="data:image/jpeg;base64,//(a variable amount of encoded data)//"></p>
<p>
Text right after the image.</p>
<p>
&nbsp;</p>

The text before as well as the text after the IMG tag will be of variable length. I'd like to replace the large amount of text within the IMG tag:

<img alt="" src="data:image/jpeg;base64,//(a variable amount of encoded data)//">

With the following text consistently:

<img src="cid:image.jpg" alt="This is a banner"/>

I use INSTR to find the start and end of the IMG tag and store them in local variables. I can't figure out how to replace by those variable positions.

I appreciate your help.

Upvotes: 0

Views: 655

Answers (1)

David Faber
David Faber

Reputation: 12485

You should be able to use REGEXP_REPLACE() to accomplish this:

SELECT REGEXP_REPLACE(mytext, '<img alt="" src="data:image/jpeg;base64,[^"]*">', '<img src="cid:image.jpg" alt="This is a banner"/>', 'i') AS mynewtext
  FROM mytable

It might be better to do the search-and-replace in your sending code rather than in the query itself.

Upvotes: 1

Related Questions