Search code examples
sqlregexoracle-databaseposixregexp-replace

Oracle POSIX dosent match Regular Expression


I am trying to replace newlines with regex. I got two databases Oralce and MySQL. The SQL statment works fine on MySQL but not with Oralce.

This is my example text:

<ul>
<li>Was soll erreicht worden sein, wenn man das Projekt durchgeführt hat?</li>
</ul>
Formulieren Sie immer konkret.
<ul>
<li><strong><span style=\"color: #800000;\">Wirtschaftliche Ziele</span></strong> (z. B.: Umsatzsteigerung im betroffenen Produktbereich: 20% im 3. Quartal des ersten Geschäftsjahres)</li>
</ul>
<ul>
<li>Test</li>
</ul>

The goal is to place an </ul><br/> after every </li></ul> where the following character is a newline NOT followed my the character <.

Working solution in MySQL :

SELECT *,
REGEXP_REPLACE(DeInfo, '(<\/ul>)(\r?\n)(?=[^<])', '</ul><br/>') as newDeInfo,
DeInfo
FROM txttrans 

Not working Oralce statement:

SELECT id, deinfo, FIRMID,
REGEXP_REPLACE(DeInfo, '(<\/ul>)(\r ? \n)(?=[^<])', '</ul><br/>', 1, 0, 'i') as TEST
FROM txttrans

Solution

  • Thanks to @Alex,

    the working solution:

    SELECT id, deinfo, FIRMID,
    REGEXP_REPLACE(DeInfo, '(</ul>)([[:space:]][^<])([^<br />]{0})', '</ul><br/>\2', 1, 0, 'i') as TEST
    FROM txttrans;