Optimal way to extract string in redshift without using regexp

Question

Lets say we want to extract the substring from url till second occurrence of /.

e.g. https://abc.def.com/abc?102/ extracted string should be abc.def.com/abc without ?102

http://abc.def/jkl/ghi/ extracted string should be abc.def/jkl

I want to achieve this without using regexp_substr/regexp_replace, which I have already tried.

Answer 1

If you specifically want to avoid regexes, you could use split_part() twice:

select split_part(url, '/', 1) || '/' || split_part(url, '/', 2)

I am unsure, however, that this would perform better than a regex-based solution. You would need to benchmark this against your real dataset.