Hi I am having issue when extracting fields from CLOB data. For one record I am not getting desired output.

The record is as below:

```
{1:F014243107336}{2:O2021216200422XXX24563}{3:{108:O2020}{121:2c02a452-5}{433:HIT}}{4:
:4A:SEC:20200901
:4B:FC5253
:4C:20042000,
:4D:XXXXXXX
:4E:RXX
:4F:RXXXX
-}{5:{CHK:87D1003B01F7}{TNG:}}{S:{SAC:}{COP:S}}<APSECSIGN>FS3sfasdfg!==</APSECSIGN>?

```

I want to extract data from tag :4A: into REF\_NUMBER.

I am using below SQL to get the data.

```
NVL(TRIM(TRANSLATE(REGEXP_REPLACE(REGEXP_SUBSTR(dbms_lob.substr(CLOB, 4000, 1 ), ':4A.?:[^:]+(:|-\})'), ':20.?:([^:]+)(:|-\})', '\1'),CHR(10)||CHR(13), ' ')),' ') AS REF_NUMBER

```

the output I am getting is "SEC". However I want to see output as SEC:20200901.

Can any one suggest what I am missing in my query or provide me correct query.