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
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)
split_part(url, '/', 2) is generally the fastest stackoverflow.com/questions/25272196/…Following query will work even for inputs like www.abc.com/hello
SELECT DISTINCT (regexp_matches(url, '/([^/]+)'))[1] theURL
FROM table;
And also it will skip empty entries
Following your substring approach, and using the first substring result to feed a second search:
select distinct substring(
substring(url, position('/' in url)+ 1)
, 0
, position('/' in substring(url, position('/' in url)+ 1))) AS theURL
from table;
Essentially what the query does is use your original result from substring to launch a search for the next \ , so then it is able to keep the text between the first two \
And if having them sorted alphabetically is important, you could add an outer query:
SELECT theURL FROM (
select distinct substring(
substring(url, position('/' in url)+ 1)
, 0
, position('/' in substring(url, position('/' in url)+ 1))) AS theURL
from table
) AS xt
ORDER BY xt.theURL;
