Extract data from a string sql

Question

How would the regexp_extract change if this data was in a column named "pages" and I want for every row that has ':old:yes:' to return the string after 'yes:' and before the next string?

PAGES (table name)

hello:ok:old:yes:age:test:jack   
hello:no:old:yes:hour:black:nancy   
hi:fine:old:yes:minute:white:jason   

As you can see ':old:yes:' is my starting point and i want the regexp_extract to return the next text before the colon. In the above example I would want the following results:

age  
hour  
minute 

Answer 1

select  regexp_extract(page,':old:yes:([^:]+)',1)
from    pages
where   page like '%:old:yes:%'

---

+--------+
| age    |
| hour   |
| minute |
+--------+