Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BF8CB9F9802 for ; Wed, 12 Sep 2007 00:16:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 04233-01-3 for ; Wed, 12 Sep 2007 00:16:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web50306.mail.re2.yahoo.com (web50306.mail.re2.yahoo.com [206.190.38.60]) by postgresql.org (Postfix) with SMTP id 65C069F9935 for ; Wed, 12 Sep 2007 00:16:16 -0300 (ADT) Received: (qmail 69166 invoked by uid 60001); 12 Sep 2007 03:16:15 -0000 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=EGfVqiGpoqRac6l9K44hR1TcpcvZvSPqRZPMx37SWGxfrzS+SDtXpvcJGaZ66lKS6LysqXBl7VS2h3kQCaEYgYNU+WZKDNQVii9Vbfs5kmr2XxQl79FtA8nWJTqEibnE9xPOGEfm6BdfTdoML+yJcd8OlV+CDXbo9636DbMfqtY=; X-YMail-OSG: o68cJOkVM1lQqQ8X.ENGNJ1p557q9seImYnzsEwOAV5Kg8e85_F5nlDQUZpO74M3Ve.ywRmaTW5YE3MTx_uwjUFFMGGDlKtnPJpLhTpz_qXTJGQ6ww-- Received: from [216.39.52.160] by web50306.mail.re2.yahoo.com via HTTP; Tue, 11 Sep 2007 20:16:15 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Tue, 11 Sep 2007 20:16:15 -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: <750084.67431.qm@web50306.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/93 X-Sequence-Number: 29306 Hi,=0A=0AThanks, perfect! (though I'll have to look into the regex warning)= :=0A=0A=3D> select substring( href from '.*://\([^/]*)' ) as hostname from = url where id<10;=0AWARNING: nonstandard 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 texturizer.net=0A forums.mozillazine.org=0A www.= mozillazine.org=0A devedge.netscape.com=0A www.google.com=0A groups.google.= com=0A www.google.com=0A dictionary.reference.com=0A=0AAnd what I'd like is= something that would give me the counts for the number of occurrences of e= ach unique hostname. Something much like `uniq -c'. Can anyone tell me ho= w that's done or where I should look for info? (I'm not sure what to look f= or, that's the problem).=0A=0AThanks,=0AOtis=0A=0A=0A----- Original Message= ----=0AFrom: chester c young =0ATo: ogjunk-pgjeda= n@yahoo.com=0ACc: sql pgsql =0ASent: Tuesday, Sep= tember 11, 2007 8:42:46 PM=0ASubject: Re: [SQL] Extracting hostname from UR= I column=0A=0A> I'm trying to use substr() and position() functions to extr= act the=0A> full host name (and later a domain) from a column that holds UR= Ls.=0A=0Asubstring( href from '.*://\([^/]*)' );=0A=0A=0A=0A =0A_____= ___________________________________________________________________________= ____=0APinpoint customers who are looking for what you sell. =0Ahttp://sear= chmarketing.yahoo.com/=0A=0A=0A