Get string between two different characters SQL vertica-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

You can also do it with SPLIT_PART, once with the slash, once with the question mark:

WITH
indata(url) AS (
  SELECT 'https://www.stackoverflow.com/abc/sometihng-herer?&jhdsfj@38736'
)
SELECT 
  SPLIT_PART(url,'/',5) AS sp5 -- it is the fifth slash-delimited token of the url
, SPLIT_PART(sp5,'?',1) AS whatwewant
FROM indata;
-- out               sp5              |   whatwewant    
-- out ------------------------------- -----------------
-- out  sometihng-herer?&jhdsfj@38736 | sometihng-herer