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

ogjunk-pgjedan@yahoo.com wrote:
>
> 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
>

Just use distinct...

test=# select distinct count(*),substring( href from '.*://([^/]*)' ) as
domain from url group by domain order by domain;
count | domain
-------+--------------------------
3 | devedge.netscape.com
1 | dictionary.reference.com
2 | forums.mozillazine.org
1 | groups.google.com
4 | texturizer.net
11 | www.google.com
2 | www.mozillazine.org
(7 rows)

--
Paul Lambert
Database Administrator
AutoLedgers

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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).
>

select substring( ... ), count(1) from your_table group by 1;




__________________________________________________ __________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545469

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Paul Lambert wrote:
>
> Just use distinct...
>
> test=# select distinct count(*),substring( href from '.*://([^/]*)' ) as
> domain from url group by domain order by domain;

OK so distinct was redundant there... it gives the same results without it.

AutoDRS=# select count(*) as occurances,substring( href from
'.*://([^/]*)' ) as domain from url group by domain order by occurances
desc,domain;
occurances | domain
------------+--------------------------
11 | www.google.com
4 | dictionary.reference.com
4 | texturizer.net
3 | devedge.netscape.com
3 | groups.google.com
2 | forums.mozillazine.org
2 | www.mozillazine.org
(7 rows)

--
Paul Lambert
Database Administrator
AutoLedgers


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster