How to pattern REGEXP_LIKE for two values?

Question

Oracle

I have an XML document

<.........>
<RRRR>
    <OfficeCode>12125589</OfficeCode>
    <OfficeName>Офис 132</OfficeName>
</RRRR>
<DocumentNumber>1234-258 (копия)</DocumentNumber>
<DocumentDate>2000-01-01</DocumentDate>
<.................>

Now the search comes with two conditions:

AND TO_CLOB(message, 873,'text/xml') LIKE '%OfficeCode>12125589<%'
AND TO_CLOB(message, 873,'text/xml') LIKE '%DocumentNumber1234-258 (копия)<%'

I teach Oracle and wanted to combine these two conditions into one REGEXP_LIKE

Tried in different ways and does not want to be in any way. Through OR works:

AND REGEXP_LIKE(TO_CLOB(message, 873,'text/xml'), 'OfficeCode>12125589<|DocumentNumber1234-258 (копия)<', 'm')

But how to do AND - I can’t understand ...

the last thing I tried:

AND REGEXP_LIKE(TO_CLOB(mes.messagebody, 873,'text/xml'), '(OfficeCode>12125589<){1}(.*)(DocumentNumber1234-258 (копия)){1}', 'm')

AND REGEXP_LIKE(TO_CLOB(mes.messagebody, 873,'text/xml'), 'OfficeCode>12125589<(.*)DocumentNumber1234-258 (копия)', 'm')

But it doesn't work like that. I don't understand what is wrong and how to make it work.

Answer 1

If you see functions for working with regular expressions in the DBMS, this does not mean that all their power has been realized.
For example, in your case, in order to implement AND, the regex executor needs to be able to handle lookback or lookahead checks.
Such functionality was not noticed behind the Oracle functions, due to the fact that this DBMS is not for full-text search, but a tool for quickly working with well-structured data.
Picking in fragments of text is not a strong point of relational DBMS, give indexed data to such systems.
Therefore, the functionality of regular functions is limited, they are implemented a little better than like.