Redshift Extract String Between Two Patterns (regexp_substr)

Question

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=moreData someddata&=somedataagain&deviceSerialNumber=deviceabcd

I've tried this

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

Haleemur Ali · Accepted Answer · 2020-06-04 14:13:57Z

8

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');

edited Jun 4, 2020 at 14:13

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

answered Oct 18, 2018 at 17:42

naba

1811 gold badge2 silver badges5 bronze badges

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

2 Comments

Haleemur Ali Over a year ago

this is the right solution (i.e. using the e param to match on the subexpression). unfortunate that this answer is buried behind others.

harshil bhatt Over a year ago

can you please let me know, How do i stop at first '&' and not let it go to end ?

nobody · Accepted Answer · 2018-06-13 16:32:41Z

1

Use occurence parameter

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

Or

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

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

edited Jun 13, 2018 at 16:32

answered Jun 13, 2018 at 16:02

nobody

11.1k8 gold badges51 silver badges72 bronze badges

3 Comments

David Over a year ago

Hmm, this looked promising, but it still seems to be extracting all the text until the final occurrence of &

nobody Over a year ago

deviceSerialNumber=(.+?)& should do it.You might have to use replace deviceSerialNumber= in the final select.

David Over a year ago

Now, I'm getting a Invalid preceding regular expression prior to repetition operator error. Got it a bunch earlier when I trying other solutions that are on the net. Wondering if it's something specific about the redshift implementation

emehex · Accepted Answer · 2018-08-15 14:31:21Z

0

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:

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

edited Aug 15, 2018 at 14:31

answered Aug 15, 2018 at 14:25

emehex

10.7k11 gold badges61 silver badges111 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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 &

select substring(pre_serial_num, 1, position('&' in pre_device_id || '&') - 1) as device_id
from
    (select substring(session_tags,position('deviceSerialNumber' in session_tags) + 20, 40) as pre_device_id 
    from table) 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

Collectives™ on Stack Overflow

Redshift Extract String Between Two Patterns (regexp_substr)

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related