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/
