From: ogjunk-pgjedan@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Extracting hostname from URI column
Date: Tue, 11 Sep 2007 20:58:16 -0700 (PDT)
Message-ID: <247444.36947.qm@web50311.mail.re2.yahoo.com> (raw)
Ah, I figured out what to look for and found my uniq -c solution:
select substring( href from '.*://([^/]*)' ) as hostname, count(substring( href from '.*://([^/]*)' )) from url where id<10 group by hostname order by count desc;
hostname | count
--------------------------+-------
texturizer.net | 2
www.google.com | 2
dictionary.reference.com | 1
www.mozillazine.org | 1
devedge.netscape.com | 1
groups.google.com | 1
forums.mozillazine.org | 1
Thanks for the quick help with substring func, people!
Otis
----- Original Message ----
From: "ogjunk-pgjedan@yahoo.com" <ogjunk-pgjedan@yahoo.com>
To: pgsql-sql@postgresql.org
Sent: Tuesday, September 11, 2007 11:16:15 PM
Subject: Re: [SQL] Extracting hostname from URI column
Hi,
Thanks, perfect! (though I'll have to look into the regex warning):
=> select substring( href from '.*://\([^/]*)' ) as hostname from url where id<10;
WARNING: nonstandard use of escape in a string literal at character 29
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
So now I have this:
hostname
--------------------------
texturizer.net
texturizer.net
forums.mozillazine.org
www.mozillazine.org
devedge.netscape.com
www.google.com
groups.google.com
www.google.com
dictionary.reference.com
And what I'd like is something that would give me the counts for the number of occurrences of each unique hostname. Something much like `uniq -c'. Can anyone tell me how that's done or where I should look for info? (I'm not sure what to look for, that's the problem).
Thanks,
Otis
----- Original Message ----
From: chester c young <chestercyoung@yahoo.com>
To: ogjunk-pgjedan@yahoo.com
Cc: sql pgsql <pgsql-sql@postgresql.org>
Sent: Tuesday, September 11, 2007 8:42:46 PM
Subject: Re: [SQL] Extracting hostname from URI column
> 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.
substring( href from '.*://\([^/]*)' );
____________________________________________________________________________________
Pinpoint customers who are looking for what you sell.
http://searchmarketing.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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: <247444.36947.qm@web50311.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