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

help / color / mirror / Atom feed

From: ogjunk-pgjedan@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Extracting hostname from URI column
Date: Tue, 11 Sep 2007 17:06:12 -0700 (PDT)
Message-ID: <335284.57764.qm@web50301.mail.re2.yahoo.com> (raw)

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

view thread (17+ messages)  latest in thread

Message-ID: <335284.57764.qm@web50301.mail.re2.yahoo.com>
Permalink:  ../335284.57764.qm@web50301.mail.re2.yahoo.com/
Also on:    postgresql.org/message-id/335284.57764.qm@web50301.mail.re2.yahoo.com

copy link · copy postgr.es

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: ogjunk-pgjedan@yahoo.com
  Subject: Re: Extracting hostname from URI column
  In-Reply-To: <335284.57764.qm@web50301.mail.re2.yahoo.com>

* 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