Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7422D9F9C0E for ; Tue, 11 Sep 2007 21:45:24 -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 16488-07 for ; Tue, 11 Sep 2007 21:45:07 -0300 (ADT) X-Greylist: delayed 00:21:14.811485 by SQLgrey-1.7.5 Received: from rrmailscan3.safenetbox.biz (mailscan3.reynolds.com.au [203.25.40.75]) by postgresql.org (Postfix) with ESMTP id E16549FA2BD for ; Tue, 11 Sep 2007 21:45:12 -0300 (ADT) Received: from rrmailscan3.safenetbox.biz (rrmailscan3.safenetbox.biz [127.0.0.1]) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id 3D4601D48447; Wed, 12 Sep 2007 10:23:56 +1000 (EST) Received: from [127.0.0.1] (online4-new.lnk.telstra.net [139.130.169.227]) (Authenticated sender: remote) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id DD2A41D48428; Wed, 12 Sep 2007 10:23:50 +1000 (EST) Message-ID: <46E7315C.3060006@autoledgers.com.au> Date: Wed, 12 Sep 2007 08:22:52 +0800 From: Paul Lambert User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: ogjunk-pgjedan@yahoo.com, pgsql-sql@postgresql.org Subject: Re: Extracting hostname from URI column References: <335284.57764.qm@web50301.mail.re2.yahoo.com> In-Reply-To: <335284.57764.qm@web50301.mail.re2.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/88 X-Sequence-Number: 29301 ogjunk-pgjedan@yahoo.com wrote: > Hi, > > 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. > This is what I'm trying, but it clearly doesn't do the job. > > => select substr(href, position('://' in href)+3, position('://' in href)+3+position('/' in href)), href from url where id <10; > substr | href > ----------------+---------------------------------------------------------- > texturizer.net | http://texturizer.net/firebird/extensions/ > texturizer.net | http://texturizer.net/firebird/themes/ > forums.mozilla | http://forums.mozillazine.org/index.php?c=4 > www.mozillazin | http://www.mozillazine.org/ > devedge.netsca | http://devedge.netscape.com/viewsource/2002/bookmarks/ > www.google.com | http://www.google.com/search?&q=%s > groups.google. | http://groups.google.com/groups?scoring=d&q=%s > www.google.com | http://www.google.com/search?q=%s&btnI=I'm+Feeling+Lucky > dictionary.ref | http://dictionary.reference.com/search?q=%s > > The 3rd param to the substr function is clearly wrong. Is it even doable without writing a procedure? > > Finally, is this the fastest way to get this data, or is there regex-based function that might be faster? > > Thanks, > Otis > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > Try this: test=# select substr(href, position('://' in href)+3, position('/' in substr( href,position('://' in href)+3))-1), href from url; substr | href --------------------------+----------------------------------------------------- ----- devedge.netscape.com | http://devedge.netscape.com/viewsource/2002/bookmark s/ texturizer.net | http://texturizer.net/firebird/extensions/ texturizer.net | http://texturizer.net/firebird/themes/ forums.mozillazine.org | http://forums.mozillazine.org/index.php?c=4 www.mozillazine.org | http://www.mozillazine.org/ devedge.netscape.com | http://devedge.netscape.com/viewsource/2002/bookmark s/ www.google.com | http://www.google.com/search?&q=%s groups.google.com | http://groups.google.com/groups?scoring=d&q=%s www.google.com | http://www.google.com/search?q=%s&btnI=I'm+Feeling+L ucky dictionary.reference.com | http://dictionary.reference.com/search?q=%s (10 rows) -- Paul Lambert Database Administrator AutoLedgers