1

I am pretty new to using string operations in SQL(Redshift). I want to extract a part of a string from the strings of the following format:

I can have strings of the format:

http://bunnytalks.com/goingOn?name=Bunny&phone=2340
http://bunnytalks.com/goingOn?name=Bunny
http://bunnytalks.com/goingOn?name=Talks/whatson%goingOn%name%Bunny
http://bunnytalks.com/goingOn?name=Talks/whatson%goingOn%name%Bunny&phone=2340

The final output I need from any of the above strings when applying regex:

Bunny

From the above string examples, I can tell that I need a string between the last occurrence of a name followed by either = or % and the end of the string or before &

I need a regex/ any string operations in SQL that can achieve the above operations as shown in examples. Thanks in advance.

Bunny Talks
  • 47
  • 5

1 Answers1

0

Try:

.*name[%=]([^&\n]+)

Regex demo.

.*name - match the last name

[%=] - followed by % or =

([^&\n]+) - match all non-&, non-\n characters as group 1

Andrej Kesely
  • 168,389
  • 15
  • 48
  • 91
  • Even `\w` would be enough: https://regex101.com/r/OB0M9r/1 – PM 77-1 Dec 06 '22 at 19:06
  • Hi, thank you. I find its working as expected in the demos shared. But when I do a Redshift query - `select regexp_substr('http://bunnytalks.com/goingOn?name=Talks/whatson%goingOn%name%Bunny','.*name[%=]([^&\n]+)')` it is not giving the expected result. I got `http://bunnytalks.com/goingOn?name=Talks/whatson%goingOn%name%Bunny` – Bunny Talks Dec 06 '22 at 19:24
  • @BunnyTalks I don't have experience with Redshift, but this answer might help https://stackoverflow.com/a/54826326/10035985 (basically, you want to get the value of first capturing group) – Andrej Kesely Dec 06 '22 at 19:27
  • Thank you @AndrejKesely, will check it. Just for additional context. I used this site - https://www.regextester.com/99656 to test the demo link you shared for SQL. – Bunny Talks Dec 06 '22 at 19:30