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

i want replace value found within regexp_replace in oracle. used regex101.com tool debug regular expression, , it's highlights result well, if put expression select, nothing... it's not replacing string want. so, question is, why pattern wrong in oracle pl/sql?

select regexp_replace('some xml datas', '/(?<=</first_tag>)(.*)(?=</last_tag>)/s', '<replace_tag xsi:nil="1"/>') dual 

as can see search between 2 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 much!

the relevant section fine manual: using regular expressions in database applications.

the idea match text around text-to-be-replaced , use backreferences match subexpressions in replace string.

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

another example op example data. note use of match parameter n:

'n' allows period (.), match-any-character character, match newline character. if omit parameter, period not match newline character.

the parameter required because string contains newline charaters. following example correctly eliminate locale_list-element. warning: please note manipulating stringified xml regular expression not recommended practice.

with data(id, str) (   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>]' dual ) select   id ,regexp_replace(str, '(.+</first_tag>).+(</last_tag>)', '\1***\2', 1, 1, 'n') str2 data ;