Redshift提取两个模式之间的字符串(regexp_substr) [英] Redshift Extract String Between Two Patterns (regexp_substr)

问题描述

我正在尝试提取 deviceSerialNumber = 之后的文本，该文本一直出现到& 或字符串的末尾.数据看起来像

I'm trying to extract the text following deviceSerialNumber= that occurs until either a & or the end of the string. Data looks like

someddata& = somedataagain& deviceSerialNumber = device12345& anotherField = moreDatasomeddata& = somedataagain& deviceSerialNumber = deviceabcd

someddata&=somedataagain&deviceSerialNumber=device12345&anotherField=moreData someddata&=somedataagain&deviceSerialNumber=deviceabcd

我已经尝试过了

REGEXP_SUBSTR(session_tags || '&', 'deviceSerialNumber(.*)&') from table

但是，这将返回所有文本，直到最后的& (这是字符串的末尾，因为我要添加& 进行模式匹配目的).我如何修改此正则表达式以仅提取文本，直到第一个& ?

but, this returns all of the text until the final & (which is the end of the string, since I'm appending a & for pattern matching purposes). How do I have to modify this regular expression to extract only the text until the first &?

推荐答案

我知道响应已晚，但这是对我有用的解决方案.

I know it is late to respond, but here is solution that worked for me.

select regexp_substr(
    'someddata&=somedataagain&deviceSerialNumber=device12345&anotherField=moreData ', 
    'deviceSerialNumber=(.*)&', 0, 1, 'e');

这篇关于Redshift提取两个模式之间的字符串(regexp_substr)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！