Redshift Extract String Between Two Patterns (regexp_substr)

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&deviceSeri

Solution 1:

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

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

Solution 2:

Use occurence parameter

REGEXP_SUBSTR(session_tags, 'deviceSerialNumber=(.+?)&',1,1) fromtable

Or

REGEXP_SUBSTR(session_tags,(?<=deviceSerialNumber=)(.*?)(?=&)) from table

.* will match till the last & and .*? will match till the first &

Solution 3:

I was running into the same Invalid preceding regular expression prior to repetition operator with regexp_substr.

The work around I eventually settled on was two nested split_parts:

selectparams, 
split_part(split_part(params, 'deviceSerialNumber=', 2), '&', 1)
from (
    select'someddata&=somedataagain&deviceSerialNumber=device12345&anotherField=moreData'asparams
    union all
    select'someddata&=somedataagain&deviceSerialNumber=deviceabcd'asparams
) tmp

Solution 4:

Found a hack solution that involves two levels of queries to get around having to use regexp_subtr. The inner query uses substring and position to pull out all of the text after the deviceSerialNumber tag. The outer query uses the same two functions to cut off any text after the next &

selectsubstring(pre_serial_num, 1, position('&'in pre_device_id ||'&') -1) as device_id
from
    (selectsubstring(session_tags,position('deviceSerialNumber'in session_tags) +20, 40) as pre_device_id 
    fromtable) a

eg the inner query takes

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

someddata&=somedataagain&deviceSerialNumber=deviceabcd

and strips text before the device serial number tag to give you

device12345&anotherField=moreData

deviceabcd

The second query then strips text after the device serial number tag to give you

deviceSerialNumber=device12345

deviceSerialNumber=deviceabcd