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

David דודו Markovitz · Accepted Answer · 2017-03-17 16:52:51Z

2

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

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

answered Mar 17, 2017 at 16:52

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sarah Over a year ago

Can you help with this question: stackoverflow.com/questions/46370135/…

Collectives™ on Stack Overflow

Extract data from a string sql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related