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.net
texturizer.net
forums.mozillazine.org
www.mozillazine.org
devedge.netscape.com
www.google.com
groups.google.com
www.google.com
dictionary.reference.com
And what I'd like is something that would give me the counts for the number of
occurrences of each unique hostname. Something much like `uniq -c'. Can
anyone tell me how that's done or where I should look for info? (I'm not sure
what to look for, that's the problem).
Thanks,
Otis
----- Original Message ----
From: chester c young <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
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
