CODEWITHSUNDEEP
sqlpostgresql
user7406308
user7406308

Reputation:

Get substring between 2 characters in PostgreSQL

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

Upvotes: 4

Views: 17626

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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)

Upvotes: 9

Jorge Y.
Jorge Y.

Reputation: 1133

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;

Upvotes: 0

khoroshevj
khoroshevj

Reputation: 1137

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

Upvotes: 0

Related Questions