Substring extract from string in postgresql

Question

The following query extracts the last two parts from a url. I want to extract the second last part from the url excluding the last.

SELECT substring(url,'/[^/]+/[^/]+$') FROM url_time;

Suppose I give input as Input : http://stackoverflow.com/questions/ask then I wish to get Output : /questions

Sorry if it is a trivial question . I have not been able to get to the required solution.

Any help is appreciated.

Politank-Z · Accepted Answer · 2015-06-29 13:46:41Z

1

regexp_replace and capture groups will do it:

SELECT REGEXP_REPLACE ( url,'^.*/([^/]+)/[^/]+$', '\1' ) FROM url_time;

The initial ^.* consumes the beginning of the url. The parenthesis () around the first [^/]+ indicate a capture group, \1 replaces the entire match with the value of the capture group in output.

edited Jun 29, 2015 at 13:46

answered Jun 29, 2015 at 13:40

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akshay Hazari Over a year ago

SELECT regexp_replace('stackoverflow.com/questions/ask','/([^/])+/[^/]+$','\1') AS action; gives me stackoverflow.coms as the answer as of now. I am trying to experiment with it though.

Politank-Z Over a year ago

Yeah, I caught that too, answered before I tested. See my edit.

Collectives™ on Stack Overflow

Substring extract from string in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related