从字符串sql提取数据 [英] Extract data from a string sql

问题描述

如果此数据位于名为"pages"的列中，并且我希望具有':old:yes:'的每一行都返回'yes:'之后和下一个字符串之前的字符串，那么regexp_extract将会如何变化?

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(表名)

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

您可以看到':old:yes:'是我的出发点，我希望regexp_extract返回冒号之前的下一个文本.在上面的示例中，我想要以下结果:

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 

推荐答案

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

---

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

这篇关于从字符串sql提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！