Search code examples
postgresqlsubstringgreenplum

GreenPlum Substring - Getting part of a long text

say I have a long URL

xyz = 'www.google.com/xyz?para1=value1&para2=value2&para3=value3....'

I am trying to get the 'para1' out of this long URL

So, I have

select TRIM(Leading '?' from Substring(xyz from '%#"?%=#"%' for '#'))

The answer I get for this particular statement is

para1=value1&para2=value2&para3=

How can I get just 'para1' using the select statement above (or any other similar method?)

I am using Greenplum (as mentioned in the topic heading)

Solution

  • Since you apparently have the regexp_ functions (I didn't think Greenplum supported them) use:

    select (regexp_matches(
   'www.google.com/xyz?para1=value1&para2=value2&para3=value3....',
   '\?([^&]+)='
   ))[1];