pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: ogjunk-pgjedan@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Extracting hostname from URI column
Date: Wed, 12 Sep 2007 08:22:52 +0800
Message-ID: <46E7315C.3060006@autoledgers.com.au> (raw)
In-Reply-To: <335284.57764.qm@web50301.mail.re2.yahoo.com>
References: <335284.57764.qm@web50301.mail.re2.yahoo.com>

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



view thread (17+ messages)  latest in thread

Message-ID: <46E7315C.3060006@autoledgers.com.au>
Permalink:  ../46E7315C.3060006@autoledgers.com.au/
Also on:    postgresql.org/message-id/46E7315C.3060006@autoledgers.com.au

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: paul.lambert@autoledgers.com.au, ogjunk-pgjedan@yahoo.com
  Subject: Re: Extracting hostname from URI column
  In-Reply-To: <46E7315C.3060006@autoledgers.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox