CODEWITHSUNDEEP
postgresqlpattern-matchingpostgresql-8.3
jzadeh
jzadeh

Reputation: 703

SQL functions to match last two parts of a URL

This is a follow up question to a post here Regex for matching last two parts of a URL

I was wondering if I could use built in sql funcitons to accomplish the same type of pattern match without using regular expressions. In particular I was thinking if there was a way to reverse the string say www.stackoverflow.com to com.stackoverflow.www and then apply concatenation to split('com.stackoverflow.www , '.', 1) || split('com.stackoverflow.www , '.', 2) I would be done but I am not sure if this is possible.

Here is the general problem description:

I am trying to figure out the best sql function simply match only the last two strings seperated by a . in a url.

For instance with www.stackoverflow.com I just want to match stackoverflow.com

The issue i have is some strings can have a large number of periods for instance

a-abcnewsplus.i-a277eea3.rtmp.atlas.cdn.yimg.com

should also return only yimg.com

The set of URLS I am working with does not have any of the path information so one can assume the last part of the string is always .org or .com or something of that nature.

What sql functions will return stackoverflow.com when run against www.stackoverflow.com and will return yimg.com when run against a-abcnewsplus.i-a277eea3.rtmp.atlas.cdn.yimg.com under the conditions stated above? I did not want to use regular expressions in the solution just sql string manipulation functions.

Upvotes: 1

Views: 4498

Answers (1)

Chris DaMour
Chris DaMour

Reputation: 4020

doesn't look like you can do it with 8.3 function set http://www.postgresql.org/docs/8.3/static/functions-string.html

there's no reverse - that comes in 9.1. with that you could do:

select reverse(split_part(reverse(data), '.', 2)) || '.'
    || reverse(split_part(reverse(data), '.', 1))
from example;

see http://sqlfiddle.com/#!1/25e43/2/0

you can declare your own reverse: http://a-kretschmer.de/diverses.shtml and then solve this problem.

but regex is just easier...

Upvotes: 9

Related Questions