Regular Expression in Redshift running PostgreSQL

Question

I have a table in redshift running PostgreSQL in which one of the columns is url.

I need to extract parameters from this url.

ie. if url=foo.bar?param1=a&param2=b&param1=c

I need the value of param1 which is c from this url

Tried a few solutions like

(string_to_array(url,'param1='))[array_upper(string_to_array(url,'param1='),1)]

and

regexp_matches

Redshift does not allow arrays, so both methods are useless. Is there a way to solve this using SQL ?

I think you need a regex like param1=([^&=\s]+)(?!.*param1) ;). — shA.t
– shA.t, Commented Oct 11, 2015 at 11:27

shA.t · Accepted Answer · 2015-10-11 11:38:40Z

1

Try to use regexp_matches like this:

SELECT regexp_matches('url=foo.bar?param1=a&param2=b&param1=c', E'param1=([^&=\s]+)(?!.*param1)');

answered Oct 11, 2015 at 11:38

shA.t

17k5 gold badges59 silver badges121 bronze badges

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

1 Answer 1