Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 68FF49F9A1F for ; Tue, 11 Sep 2007 21:06:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 23392-01 for ; Tue, 11 Sep 2007 21:06:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web50301.mail.re2.yahoo.com (web50301.mail.re2.yahoo.com [206.190.38.55]) by postgresql.org (Postfix) with SMTP id A06219F9A18 for ; Tue, 11 Sep 2007 21:06:13 -0300 (ADT) Received: (qmail 57801 invoked by uid 60001); 12 Sep 2007 00:06:12 -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=RSrkgFs9XjaQ+mMSjY4+E/77o4z9a4fWIFXf5PJVyBsdSoyjN9Dn6e+jQQHWYF1QndPclQjOjUj2U+6FOTwEHZu4PaZyDLFy83BgjqdQhzbkpe3QsW7j5C3U0WN21E2RzTfcnk0/UpOuzpYgCwJaUQ6+VCa8zOR6ObS8e1+lJNU=; X-YMail-OSG: JynV4AEVM1kxbl.9QMnmimcc51Kowj.hW0HP1YtbCrbZLIuleLpQOrBws0nGZDf5SCPmGOBkgxxVgrrlyJUR2Eooc9xdFRslpXtlAmrwAkTeiNqh4pw- Received: from [216.252.108.190] by web50301.mail.re2.yahoo.com via HTTP; Tue, 11 Sep 2007 17:06:12 PDT X-Mailer: YahooMailRC/651.50 YahooMailWebService/0.7.134 Date: Tue, 11 Sep 2007 17:06:12 -0700 (PDT) From: ogjunk-pgjedan@yahoo.com Subject: 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: <335284.57764.qm@web50301.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/86 X-Sequence-Number: 29299 Hi,=0A=0AI'm trying to use substr() and position() functions to extract the= full host name (and later a domain) from a column that holds URLs.=0AThis = is what I'm trying, but it clearly doesn't do the job.=0A=0A=3D> select sub= str(href, position('://' in href)+3, position('://' in href)+3+position('/'= in href)), href from url where id <10;=0A substr | = href=0A----------------+----------------------------------------= ------------------=0A texturizer.net | http://texturizer.net/firebird/exten= sions/=0A texturizer.net | http://texturizer.net/firebird/themes/=0A forums= .mozilla | http://forums.mozillazine.org/index.php?c=3D4=0A www.mozillazin = | http://www.mozillazine.org/=0A devedge.netsca | http://devedge.netscape.c= om/viewsource/2002/bookmarks/=0A www.google.com | http://www.google.com/sea= rch?&q=3D%s=0A groups.google. | http://groups.google.com/groups?scoring=3Dd= &q=3D%s=0A www.google.com | http://www.google.com/search?q=3D%s&btnI=3DI'm+= Feeling+Lucky=0A dictionary.ref | http://dictionary.reference.com/search?q= =3D%s=0A=0AThe 3rd param to the substr function is clearly wrong. Is it ev= en doable without writing a procedure?=0A=0AFinally, is this the fastest wa= y to get this data, or is there regex-based function that might be faster?= =0A=0AThanks,=0AOtis=0A=0A