SQL & PL/SQL

Hello. I need help with Regular Expressions on a Oracle 11g database.

Within a clob, size can be over 32K, I have a text document that was converted from xml to html. However, the conversion missed some items. One that is giving me trouble is a xml tag used to signify deleted text from a prior version. The target xml tag is: <?xm-deletion_mark author="conversion process" time="20110113T145417-0800" data=" interscholastic"?>. This can appear anywhere within the text and can occur multiple times with different embedded text and with different timestamps.

What is odd is that the deleted text is an attribute of the xml tag rather than a text node as one would expect. Above I have only a word of text "interscholastic" that I need to preserve, but the text could be 100 pages. What I need to do is transform the above xml to an html tag such that I end up with >STRIKE< interscholastic>/STRIKE<. So, the first part of the tag would become the >STRIKE< and the trailing "?> would become >/STRIKE< and the attribute data would be place between the two html tags sans the double quotes.

I could loop through the text document and replace each occurrence, but such a brute force approach, using pointers, seems more difficult that it needs to be. I believe that the appropriate use of RegExp_Replace could be used instead to convert this tag. Similar to what I did for the Insert tag:

UPDATE Measure_Version
SET Digest =
RegExp_Replace(Digest, '<\?xm-insertion_mark_start\sauthor="conversion process"\stime="(\d){8}T(\d){6}-(\d){4}"\?>', '<U>')
WHERE RegExp_Like (Digest, '<\?xm-insertion_mark_start\sauthor="conversion process"\stime="(\d){8}T(\d){6}-(\d){4}"\?>', 'c');

Thank you for any help you could provide I would greatly appreciate. the original message did not preserve the text properly, the replacement tag for "STRIKE", so I reverse the brackets to post.

Edited by: user13379403 on Mar 22, 2011 10:56 AM

Edited by: user13379403 on Mar 22, 2011 10:59 AM