DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=BkfSHeeIM1FZ3NuEdwzfZgoheDmIC2+Ky6QkpPPM7uCVyavOXVsYBi25Yc1+tTzdq0CPj5DoQ27ZFJrgILFq6vTOqlJKpTwHboeUf95J30d60bZpsb/Kr4QXbJ9xjNQYR95l73Rr+S33humllscnrxDtSyPdAa7G6v+4l3+QMrQ=;
Date: Tue, 11 Sep 2007 20:58:16 -0700 (PDT)
From: ogjunk-pgjedan@yahoo.com
Subject: Re: Extracting hostname from URI column
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-ID: <247444.36947.qm@web50311.mail.re2.yahoo.com>

Ah, I figured out what to look for and found my uniq -c solution:=0A=0Asele=
ct substring( href from '.*://([^/]*)' ) as hostname, count(substring( href=
 from '.*://([^/]*)' )) from url where id<10 group by hostname order by cou=
nt desc;=0A         hostname         | count=0A--------------------------+-=
------=0A texturizer.net           |     2=0A www.google.com           |   =
  2=0A dictionary.reference.com |     1=0A www.mozillazine.org      |     1=
=0A devedge.netscape.com     |     1=0A groups.google.com        |     1=0A=
 forums.mozillazine.org   |     1=0A=0AThanks for the quick help with subst=
ring func, people!=0A=0AOtis=0A=0A=0A----- Original Message ----=0AFrom: "o=
gjunk-pgjedan@yahoo.com" <ogjunk-pgjedan@yahoo.com>=0ATo: pgsql-sql@postgre=
sql.org=0ASent: Tuesday, September 11, 2007 11:16:15 PM=0ASubject: Re: [SQL=
] Extracting hostname from URI column=0A=0AHi,=0A=0AThanks, perfect! (thoug=
h I'll have to look into the regex warning):=0A=0A=3D> select substring( hr=
ef from '.*://\([^/]*)' ) as hostname from url where id<10;=0AWARNING:  non=
standard use of escape in a string literal at character 29=0AHINT:  Use the=
 escape string syntax for escapes, e.g., E'\r\n'.=0A=0ASo now I have this:=
=0A         hostname=0A--------------------------=0A texturizer.net=0A text=
urizer.net=0A forums.mozillazine.org=0A www.mozillazine.org=0A devedge.nets=
cape.com=0A www.google.com=0A groups.google.com=0A www.google.com=0A dictio=
nary.reference.com=0A=0AAnd what I'd like is something that would give me t=
he 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).=0A=0AT=
hanks,=0AOtis=0A=0A=0A----- Original Message ----=0AFrom: chester c young <=
chestercyoung@yahoo.com>=0ATo: ogjunk-pgjedan@yahoo.com=0ACc: sql pgsql <pg=
sql-sql@postgresql.org>=0ASent: Tuesday, September 11, 2007 8:42:46 PM=0ASu=
bject: Re: [SQL] Extracting hostname from URI column=0A=0A> I'm trying to u=
se substr() and position() functions to extract the=0A> full host name (and=
 later a domain) from a column that holds URLs.=0A=0Asubstring( href from '=
.*://\([^/]*)' );=0A=0A=0A=0A       =0A____________________________________=
________________________________________________=0APinpoint customers who a=
re looking for what you sell. =0Ahttp://searchmarketing.yahoo.com/=0A=0A=0A=
=0A=0A---------------------------(end of broadcast)------------------------=
---=0ATIP 6: explain analyze is your friend=0A=0A=0A