Redshift - Split column to find value between delimiter with uncertain position

Question

I have a table in redshift with two columns, id and link.

Current Table

id link

1 www.something.com/bla?lughlegh &fruit=apple& khkhflkjndkj&ljghldh&ljhrleh

2 www.somethingelse.com/blabla?sdf&hthdth&sdhfhfY &fruit=orange& ergegertg

I want to pick the value fruit=apple from the link column. But the catch is -

• &fruit=apple& can appear anywhere in the link [In first link fruit is between 1st and 2nd & symbol. But, in second link, fruit is between 3rd and 4th & symbol.]

• There are many &. So I cannot say that I always want the value between 3rd and 4th & symbol.

• Number of & symbols in the link is not always constant

• The value of fruit can be anything (not apple always)

I have to basically look for the text fruit= and pick values between fruit= and the next &

Required table

id link fruit

1 www.something.com/bla?lughlegh &fruit=apple& khkhflkjndkj&ljghldh&ljhrleh apple

2 www.somethingelse.com/blabla?sdf&hthdth&sdhfhfY &fruit=orange& ergegertg orange

Answer 1

Try REGEXP_REPLACE:

REGEXP_REPLACE(url, ".*[?&]fruit=([^&]*).*", "$1");