I'm trying to get a regex to capture the base URL from a URL string. This
^(.+?[^\/:])(?=[?\/]|$)
works. REGEX101
But when I try to use it within postgresql
regexp_replace(content_url,'^(.+?[^\\/:])(?=[?\\/]|$)', '\1')
it does not
Add a comment
|
2 Answers
RegexBuddy gives this warning about the first '?'
PostgreSQL is inconsistent in the way it handles lazy quantifiers in regular expressions with alternation because it attempts to match the longest alternative, instead of being eager and accepting the first alternative that matches
and if you remove it, it seems to work, i.e ^(.+[^\/:])(?=[?\/]|$)
however, if you're trying to parse the baseurl that regex won't work. Use this instead:
select regexp_replace('....', '^(.*:)//([a-z\-.]+)(:[0-9]+)?(.*)$', '\2')
5 Comments
1252748
SELECT regexp_replace('http://stackoverflow.com/questions/1991608/find-base-name-in-url-in-javascript','^(.+[^\/:])(?=[?\/]|$)', '\1') AS content_url; just gives me a box. Like a little "unknown character" box.
gordy
@thomas maybe it's the escaping, works here sqlfiddle.com/#!15/cfab1/4/0
1252748
Can you provide another solution that will work with postgre?
1252748
[Error Code: 0, SQL State: 2201B] ERROR: invalid regular expression: invalid character range :(gordy
what version of postgres are you using?
PostGreSQL has an interesting regular expression engine. It took me a while to figure out what be escaped and what needs to be double-escaped. The solution that worked for me is:
(regexp_matches(content_url,'(https?:\/\/\\w+(?:\\.\\w+)+)'))[1] AS content_url
Hope this can help someone.