SQL & PL/SQL

Hi ,

I have a query in hand where in i need to get a bit tricky result as output.

The query is :-

with t as(select '\n\nTest: 01if055053yu.demo.com sduinu 478 Value: 91.2 <= 120\n\n metadata rest:34rjudo\uki 256 adsd Value: 91.2 <= 120 yh237*hdu:udnk' col1 from dual union all

      select '\\n\\nTest: 2wedrfyu.trsthji76e 2898kl 34 yhcjk Value: 94 \<= 120  \\n metadata host:err  690 ud45o\\udssdsd Value: 94 \<= 120  \\nritujku73yhrt ' from dual  union all

      select '\\n\\nTest: 2wedrfyu.trsd23thji76eyhcjk Value: 23.4567 \<= 120qwedr metadata think:3rjuffdo\\ukiwsd Value: 23.4567 \<= 120qwe erff' from dual

     )

select col1 from t;

From this data set i need to make an output which look like this

Is there a way i can achieve it through any mechanism in oracle. I tried to achieve it regex_substr but failed in most of the scenarios. And, i'm bit confused in how to use it also.

Can you guys help on this, please..

Thanks