By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with your_table ( your_column) as (
select 'Color: red to blue (for key value : 1 ,Model : qka, Color: red)' from dual union all
select 'Model: abc1 to abc (for key Model : abc1, Color: red white)' from dual union all
select 'Model: BNM TO BNV, Color: green (for key value : 1, Color: orange, Model : BNM)' from dual union all
select 'Model: ZXC to ZXV(for key value : 1, Color: orange, Model : ZXC)' from dual
)
select your_column
, trim(
regexp_substr(your_column, '(Color\s*:\s*[^[:punct:]]+)', 1, 1, 'i', 1)
) your_output
from your_table
;
| YOUR_COLUMN | YOUR_OUTPUT |
|---|---|
| Color: red to blue (for key value : 1 ,Model : qka, Color: red) | Color: red to blue |
| Model: abc1 to abc (for key Model : abc1, Color: red white) | Color: red white |
| Model: BNM TO BNV, Color: green (for key value : 1, Color: orange, Model : BNM) | Color: green |
| Model: ZXC to ZXV(for key value : 1, Color: orange, Model : ZXC) | Color: orange |
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |