By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t as (select 'random foobar "name" : "Jack Ryan", other random stuff' col from dual)
select
col,
regexp_replace(col, '.*"name" : "([^"]+)".*', '\1') newcol1,
regexp_substr(col, '"name" : "([^"]+)"', 1, 1, null, 1) newcol2
from t
| COL | NEWCOL1 | NEWCOL2 |
|---|---|---|
| random foobar "name" : "Jack Ryan", other random stuff | Jack Ryan | Jack Ryan |