Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table MY_Table (ROW_ID, EVENT, NOTE) as
select 1, 'note', to_clob('<table>
<tr><td>test 1</td><td>ignore 1</td></tr>
<tr><td>test 2</td><td>ignore 2</td></tr>
</table><br><p>ignore</p>')
from dual
union all
select 2, 'note', to_clob('<table>
<tr><td>test 3</td><td>ignore 1</td></tr>
<tr><td>test 4</td><td>ignore 2</td></tr>
</table>')
from dual
2 rows affected

select SUBSTR(qe.NOTE, 0, INSTR(qe.NOTE, '<br>')-1)
from my_table qe;


SUBSTR(QE.NOTE,0,INSTR(QE.NOTE,'<BR>')-1)


<table>
<tr><td>test 1</td><td>ignore 1</td></tr>
<tr><td>test 2</td><td>ignore 2</td></tr>
</table>

null


with tbl as
(
    SELECT ROW_ID,xmltype(SUBSTR(qe.NOTE, 0, INSTR(qe.NOTE, '<br>')-1)) xml_data
    FROM MY_Table qe
    WHERE EVENT='note' 
)
select
    ROW_ID,rtrim (xmlagg (xmlelement (e, x.data
        || '|')
      ORDER BY x.row_number).extract ('//text()'), '|') AS Data   
from
    tbl
cross join
    xmltable('/table/tr'
        passing tbl.xml_data
        columns
            row_number FOR ORDINALITY,
            data varchar2(2000) path 'td[1]'
            
    ) x
WHERE x.data IS NOT NULL
GROUP BY ROW_ID
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

with tbl as
(
    SELECT ROW_ID,
      xmltype(
        CASE
          WHEN INSTR(qe.NOTE, '<br>') > 0
          THEN SUBSTR(qe.NOTE, 0, INSTR(qe.NOTE, '<br>')-1)
          ELSE qe.NOTE
        END
      ) xml_data
    FROM MY_Table qe
    WHERE EVENT='note' 
)
select
    ROW_ID,rtrim (xmlagg (xmlelement (e, x.data
        || '|')
      ORDER BY x.row_number).extract ('//text()'), '|') AS Data   
from
    tbl
cross join
    xmltable('/table/tr'
        passing tbl.xml_data
        columns
            row_number FOR ORDINALITY,
            data varchar2(2000) path 'td[1]'
            
    ) x
WHERE x.data IS NOT NULL
GROUP BY ROW_ID


ROW_ID
DATA


1
test 1|test 2

2
test 3|test 4


with tbl as
(
    SELECT ROW_ID,
      xmltype(SUBSTR(qe.NOTE, INSTR(qe.NOTE, '<table>'), INSTR(qe.NOTE, '</table>') + 7)) xml_data
    FROM MY_Table qe
    WHERE EVENT='note'
    AND INSTR(qe.NOTE, '<table>') > 0
)
select
    ROW_ID,rtrim (xmlagg (xmlelement (e, x.data
        || '|')
      ORDER BY x.row_number).extract ('//text()'), '|') AS Data   
from
    tbl
cross join
    xmltable('/table/tr'
        passing tbl.xml_data
        columns
            row_number FOR ORDINALITY,
            data varchar2(2000) path 'td[1]'
            
    ) x
WHERE x.data IS NOT NULL
GROUP BY ROW_ID


ROW_ID
DATA


1
test 1|test 2

2
test 3|test 4