CODEWITHSUNDEEP
sqlregexpostgresql
dogvscat
dogvscat

Reputation: 13

Substring selection within specific repetition of characters

I've been looking around and I've almost arrived at an answer but I just can't put my finger on it.

I'd like to select values after an nth forward slash and before the next forward slash in a URL.

So, for example: In the following URL... https://stackoverflow.com/foo/bar/thisValue/more/text/ I'd like to return thisValue (after the fifth forward slash).

Any ideas would be appreciated.

Upvotes: 1

Views: 366

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659227

The currently accepted solution didn't work for me. This does:

SELECT substring('http://stackoverflow.com/foo/bar/thisValue/more/text/'
                ,'^http://(?:[^/]*/){3}([^/]+)')

Explain:

^       .. anchor left  
(?:     .. non-capturing parenthesis  
[^/]*   .. 0-n character being not "/"  
{3}     .. last element 3 times  
([^/]+) .. 1-n characters not "/", this time in capturing parenthesis

More in the manual.

Upvotes: 1

mu is too short
mu is too short

Reputation: 434975

You could skip the regex and use split_part:

Split string on delimiter and return the given field (counting from one).

For example:

=> select split_part('http://stackoverflow.com/foo/bar/thisValue/more/text/', '/', 6);
 split_part 
------------
 thisValue

Don't forget the empty part caused by the doubled slashes in http://.

Upvotes: 2

foundry
foundry

Reputation: 31745

http:\/\/([^\/]+\/){n}([^\/]+).*$

n=3 in your example

Upvotes: 0

Related Questions