How to extract value between two strings with regexp_replace in Oracle?

Question

I want to replace the value found within REGEXP_REPLACE in Oracle. I used the regex101.com tool to debug my regular expression, and it's highlights the result as well, but if I put this expression into my select, it does nothing... it's not replacing the string I want. So, my question is, why is this pattern WRONG in Oracle PL/SQL?

select REGEXP_REPLACE('SOME XML DATAS', '/(?<=</FIRST_TAG>)(.*)(?=</LAST_TAG>)/s', '<REPLACE_TAG xsi:nil="1"/>') from dual

As you can see I search between two closing tags.

Sample XML:

<?xml version='1.0' encoding='UTF-8'?>
<LAST_TAG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:bla-bla-bla">
 <DEFAULT_LANGUAGE>en</DEFAULT_LANGUAGE>
 <DEBUG>0</DEBUG>
 <DEBUG_LEVEL>MEDIUM</DEBUG_LEVEL>
 <DEBUG_FILE>bla-bla.log</DEBUG_FILE>
 <FIRST_TAG>
  <LOGICAL_PRINTER>
   <ID>PRINTER1</ID>
   <PHYSICAL_PRINTER>Dummy_Printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT01</ID>
   <PHYSICAL_PRINTER>PRINT01</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT012</ID>
   <PHYSICAL_PRINTER>PRINT02</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT015</ID>
   <PHYSICAL_PRINTER>PRINT05</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT019</ID>
   <PHYSICAL_PRINTER>PRINT019</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT010</ID>
   <PHYSICAL_PRINTER>PRINT010</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID01</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT011</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID03</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT013</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID04</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT014</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID05</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT015</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>BUDCOLOR</ID>
   <PHYSICAL_PRINTER>\\url\printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>NAMENAME</ID>
   <PHYSICAL_PRINTER>\\url\printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
 </FIRST_TAG>
 <LOCALE_LIST>
  <LOCALE>
   <DISPLAY_NAME>English (United States)</DISPLAY_NAME>
   <COUNTRY>US</COUNTRY>
   <LANGUAGE>en</LANGUAGE>
   <VARIANT xsi:nil="1"/>
  </LOCALE>
 </LOCALE_LIST>
</LAST_TAG>

Thank you very much!

...REGEXP_REPLACE in Oracle. I used the regex101.com tool... *Facepalm* Please always cross check the documentation to see what you can use. — nhahtdh
– nhahtdh, Commented Jul 27, 2015 at 11:36
I forgot to mention I'm total noob to regular expressions, so no need to burn me. — VORiAND
– VORiAND, Commented Jul 27, 2015 at 12:04
The point here is that regular expressions have slightly different dialects and implementations so a regexp working in one tool might not in another tool. — user272735
– user272735, Commented Jul 27, 2015 at 12:27

user272735 · Accepted Answer · 2015-07-27 13:14:51Z

2

The relevant section from the fine manual: Using Regular Expressions in Database Applications.

The idea is to match text around the text-to-be-replaced and use backreferences to match subexpressions in a replace string.

with data(id, str) as (
  select 1, 'foo is bar' from dual union all
  select 2, 'foo was bar' from dual union all
  select 3, 'foo might be or might not be bar' from dual
)
select 
 id
,str
,regexp_replace(str, '^(foo).+(bar)$', '\1***\2') as str2
from data
;

Another example with OP example data. Note the use of match parameter n:

'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, then the period does not match the newline character.

The parameter is required because the string contains newline charaters. The following example will correctly eliminate LOCALE_LIST-element. Warning: please note manipulating stringified XML with a regular expression is not a recommended practice.

with data(id, str) as (
  select 1, q'[<?xml version='1.0' encoding='UTF-8'?>
<LAST_TAG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:bla-bla-bla">
 <DEFAULT_LANGUAGE>en</DEFAULT_LANGUAGE>
 <FIRST_TAG>
  <LOGICAL_PRINTER>
   <ID>PRINTER1</ID>
   <PHYSICAL_PRINTER>Dummy_Printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
 </FIRST_TAG>
 <LOCALE_LIST>
  <LOCALE>
   <DISPLAY_NAME>English (United States)</DISPLAY_NAME>
   <VARIANT xsi:nil="1"/>
  </LOCALE>
 </LOCALE_LIST>
</LAST_TAG>]' from dual
)
select 
 id
,regexp_replace(str, '(.+</FIRST_TAG>).+(</LAST_TAG>)', '\1***\2', 1, 1, 'n') as str2
from data
;

edited Jul 27, 2015 at 13:14

answered Jul 27, 2015 at 12:05

user272735

10.6k9 gold badges72 silver badges100 bronze badges

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

4 Comments

VORiAND Over a year ago

In this case: ^(</LOGICAL_PRINTERS>).+(</PRINT_AGENT>)$? It's not working sadly :( I tried with the escaped version as well: ^(<\/LOGICAL_PRINTERS>).+(<\/PRINT_AGENT>)$

nhahtdh Over a year ago

@VORiAND: Drop the ^ and $. They are start and end of (input/line? not sure which one) anchor.

VORiAND Over a year ago

Still nothing :( (<\/LOGICAL_PRINTERS>).+(<\/PRINT_AGENT>)

user272735 Over a year ago

@VORiAND if you still have problems to apply the solution to your special case please provide a minimal but exact string where the replacement should be applied.

Arkadiusz Łukasiewicz · Accepted Answer · 2015-07-27 13:39:01Z

Maybe you try process your xml like xmldocument instead of like text.

declare 
 v xmltype:= xmltype('<?xml version=''1.0'' encoding=''UTF-8''?>
<LAST_TAG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <DEFAULT_LANGUAGE>en</DEFAULT_LANGUAGE>
 <DEBUG>0</DEBUG>
 <DEBUG_LEVEL>MEDIUM</DEBUG_LEVEL>
 <DEBUG_FILE>bla-bla.log</DEBUG_FILE>
 <FIRST_TAG>
  <LOGICAL_PRINTER>
   <ID>PRINTER1</ID>
   <PHYSICAL_PRINTER>Dummy_Printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT01</ID>
   <PHYSICAL_PRINTER>PRINT01</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT012</ID>
   <PHYSICAL_PRINTER>PRINT02</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT015</ID>
   <PHYSICAL_PRINTER>PRINT05</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT019</ID>
   <PHYSICAL_PRINTER>PRINT019</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT010</ID>
   <PHYSICAL_PRINTER>PRINT010</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID01</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT011</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID03</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT013</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID04</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT014</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>PRINT_ID05</ID>
   <PHYSICAL_PRINTER>\\111.111.111.111\PRINT015</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>BUDCOLOR</ID>
   <PHYSICAL_PRINTER>\\url\printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
  <LOGICAL_PRINTER>
   <ID>NAMENAME</ID>
   <PHYSICAL_PRINTER>\\url\printer</PHYSICAL_PRINTER>
  </LOGICAL_PRINTER>
 </FIRST_TAG>
 <LOCALE_LIST>
  <LOCALE>
   <DISPLAY_NAME>English (United States)</DISPLAY_NAME>
   <COUNTRY>US</COUNTRY>
   <LANGUAGE>en</LANGUAGE>
   <VARIANT xsi:nil="1"/>
  </LOCALE>
 </LOCALE_LIST>
</LAST_TAG>');
begin 
 v := v.deleteXML('/LAST_TAG/DEBUG_FILE/following-sibling::*','xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"').appendChildXML('/LAST_TAG',xmltype('<NEW_TAG/>'));
 dbms_output.put_line(v.getClobVal());
end;

Collectives™ on Stack Overflow

How to extract value between two strings with regexp_replace in Oracle?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related