How to extract text between two words in Oracle

This is the source string:

random foobar "name" : "Jack Ryan", other random stuff

In Oracle query, how do I extract Jack Ryan from it?

I guess I am looking for whatever is between "name" : " and ",

Answer

One option is regexp_replace():

regexp_replace(col, '.*"name" : "([^"]+)".*', '1')

You can also use regexp_substr():

regexp_substr(col, '"name" : "([^"]+)"', 1, 1, null, 1)

This captures the portion of the string within double quotes that follows string '"name" : '.

Demo on DB Fiddle:

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