Ah, I figured out what to look for and found my uniq -c solution:
select substring( href from '.*://([^/]*)' ) as hostname, count(substring( href from '.*://([^/]*)' )) from url where
id<10group by hostname order by count desc; hostname | count
--------------------------+-------texturizer.net | 2www.google.com |
2dictionary.reference.com| 1www.mozillazine.org | 1devedge.netscape.com | 1groups.google.com
| 1forums.mozillazine.org | 1
Thanks for the quick help with substring func, people!
Otis
----- Original Message ----
From: "ogjunk-pgjedan@yahoo.com" <ogjunk-pgjedan@yahoo.com>
To: pgsql-sql@postgresql.org
Sent: Tuesday, September 11, 2007 11:16:15 PM
Subject: Re: [SQL] Extracting hostname from URI column
Hi,
Thanks, perfect! (though I'll have to look into the regex warning):
=> select substring( href from '.*://\([^/]*)' ) as hostname from url where id<10;
WARNING: nonstandard use of escape in a string literal at character 29
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
So now I have this: hostname
--------------------------texturizer.nettexturizer.netforums.mozillazine.orgwww.mozillazine.orgdevedge.netscape.comwww.google.comgroups.google.comwww.google.comdictionary.reference.com
And what I'd like is something that would give me the counts for the number of occurrences of each unique hostname.
Somethingmuch like `uniq -c'. Can anyone tell me how that's done or where I should look for info? (I'm not sure what
tolook for, that's the problem).
Thanks,
Otis
----- Original Message ----
From: chester c young <chestercyoung@yahoo.com>
To: ogjunk-pgjedan@yahoo.com
Cc: sql pgsql <pgsql-sql@postgresql.org>
Sent: Tuesday, September 11, 2007 8:42:46 PM
Subject: Re: [SQL] Extracting hostname from URI column
> I'm trying to use substr() and position() functions to extract the
> full host name (and later a domain) from a column that holds URLs.
substring( href from '.*://\([^/]*)' );
____________________________________________________________________________________
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend