SQL replace string by variable character position

Question

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.

David Faber · Accepted Answer · 2014-12-29 03:25:11Z

1

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.

answered Dec 29, 2014 at 3:25

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

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

1 Comment

David Faber Over a year ago

You're welcome. Regular expressions are great for this sort of thing and Oracle has a decent regex engine.

Collectives™ on Stack Overflow

SQL replace string by variable character position

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related