Re: Extracting hostname from URI column

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