Re: 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/