Get substring between 2 characters in PostgreSQL

Question

I am trying to get the characters between a URL like so in postgreSQL:

www.abc.com/hello/xyz

www.abc.com/hi/pqr

www.abc.com/yellow/xyz

I want to get

hello

hi

yellow

This is what I have so far:

select distinct substring(url, position('/' in url)+ 1) theURL from table;

I am only able to get the first "/"

I am not sure how to get the position of the second one

Answer 1

One method uses regexp_split_to_array():

select (regexp_split_to_array(url, '/'::text))[2]

or better yet as @NeilMcGuigan suggests:

select split_part(url, '/', 2)