Get string between two different characters SQL vertica

Question

My data is URL

https://www.stackoverflow.com/abc/sometihng-herer?&jhdsfj@38736

Output I am looking is from 3rd occurance of '/' till before '?'

sample output:

 /abc/sometihng-herer

Database is vertica and datatype is long char

Answer 1

We can use a regex substring approach here:

SELECT url, REGEXP_SUBSTR(url, 'https?://[^/]+(/[^?]+)', 1, 1, '', 1) AS path
FROM yourTable;

Here is a regex demo showing that the logic is working.