pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder by email address by domain ?
6+ messages / 5 participants
[nested] [flat]
* Order by email address by domain ?
@ 2001-05-10 18:37 Hervé Piedvache <herve@elma.fr>
2001-05-10 19:47 ` Re: Order by email address by domain ? Bruce Momjian <pgman@candle.pha.pa.us>
2001-05-11 01:53 ` Re: Order by email address by domain ? Frank Bax <fbax@sympatico.ca>
0 siblings, 2 replies; 6+ messages in thread
From: Hervé Piedvache @ 2001-05-10 18:37 UTC (permalink / raw)
To: pgsql-sql
Hi,
I just want to order by a listing of email address by domain like :
toto@aol.com
tutu@aol.com
toto@be.com
tutu@be.com
toto@yahoo.com
Is it possible and how ?
Thanks !
--
Hervé Piedvache
Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve@elma.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Order by email address by domain ?
2001-05-10 18:37 Order by email address by domain ? Hervé Piedvache <herve@elma.fr>
@ 2001-05-10 19:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2001-05-10 19:47 UTC (permalink / raw)
To: herve@elma.fr; +Cc: pgsql-sql
[ Charset ISO-8859-1 unsupported, converting... ]
> Hi,
>
> I just want to order by a listing of email address by domain like :
>
> toto@aol.com
> tutu@aol.com
> toto@be.com
> tutu@be.com
> toto@yahoo.com
>
> Is it possible and how ?
Good question. I have shell script I use to look for network duplicates
in my SPAM database so I can block even more networks. What I do is run
the program through 'rev' (on BSD/OS, not sure about other OS's) that
reverses the characters on every line, then run it though sort, the use
'rev' again to unreverse them. It groups duplicate hosts/networks
together, but does not order things properly:
x@bc.com
x@ad.com
ad is after ac because the sorting is done on moc.cb@x and moc.da@x, and
c is before d.
Not sure if that helps, but that I what I did. Not sure how to do that
in the database. Seems you will have to strip off the username@ part
and sort just the host name, then put it back.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Order by email address by domain ?
2001-05-10 18:37 Order by email address by domain ? Hervé Piedvache <herve@elma.fr>
@ 2001-05-11 01:53 ` Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Frank Bax @ 2001-05-11 01:53 UTC (permalink / raw)
To: pgsql-sql; +Cc: herve@elma.fr
Why is everyone else suggesting new functions? This works (in 6.5.3):
ORDER BY lower(substring(email from position('@' in email)+1 )),
lower(email)
remove the lower() functions if you don't need them (I had mixed case
addresses).
I am guessing/assuming that it's cheaper to just use entire email address
in second key rather than extract before the '@' character.
Frank
At 08:37 PM 5/10/01 +0200, you wrote:
>Hi,
>
>I just want to order by a listing of email address by domain like :
>
>toto@aol.com
>tutu@aol.com
>toto@be.com
>tutu@be.com
>toto@yahoo.com
>
>Is it possible and how ?
>
>Thanks !
>--
>Hervé Piedvache
>
>Elma Ingenierie Informatique
>6, rue du Faubourg Saint-Honoré
>F-75008 - Paris - France
>http://www.elma.fr
>Tel: +33-1-44949901
>Fax: +33-1-44949902
>Email: herve@elma.fr
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Order by email address by domain ?
@ 2001-05-10 21:14 Jeff Eckermann <jeckermann@verio.net>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Eckermann @ 2001-05-10 21:14 UTC (permalink / raw)
To: 'herve@elma.fr' <herve@elma.fr>; pgsql-sql
I assume that you want to do this within the database, i.e. with a query.
You would need to use a function, but I don't think a builtin function would
do it.
Try:
CREATE FUNCTION email_order (text) RETURNS text AS '
select substr ($1, strpos ($1, ''@'') + 1) || substr ($1, 1, strpos ($1,
''@'') -1)
' LANGUAGE 'sql';
Then do:
SELECT ..... FROM ... ORDER BY email_order (fieldname);
I am assuming text datatype; substitute as appropriate.
That's doubled single quotes around the @ symbol, by the way.
> -----Original Message-----
> From: Hervé Piedvache [SMTP:herve@elma.fr]
> Sent: Thursday, May 10, 2001 1:38 PM
> To: pgsql-sql@postgresql.org
> Subject: Order by email address by domain ?
>
> Hi,
>
> I just want to order by a listing of email address by domain like :
>
> toto@aol.com
> tutu@aol.com
> toto@be.com
> tutu@be.com
> toto@yahoo.com
>
> Is it possible and how ?
>
> Thanks !
> --
> Hervé Piedvache
>
> Elma Ingenierie Informatique
> 6, rue du Faubourg Saint-Honoré
> F-75008 - Paris - France
> http://www.elma.fr
> Tel: +33-1-44949901
> Fax: +33-1-44949902
> Email: herve@elma.fr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Order by email address by domain ?
@ 2001-05-11 07:45 Maxim Maletsky <maxim@japaninc.com>
0 siblings, 0 replies; 6+ messages in thread
From: Maxim Maletsky @ 2001-05-11 07:45 UTC (permalink / raw)
To: 'herve@elma.fr' <herve@elma.fr>; pgsql-sql
here's an idea:
if you would have all emails stored in the reverse order:
moc.niamod@resu
then all you would need is simple 'ORDER BY email'.
Also, in this way, emails like user@subdomain.domain.com would be perfectly
ordered.
Is that a crazy thought?
If not yet then: why not to add an additional field to the table with the
reverse domain only? (after an @) ordering by it. INDEX would also be
simple.
now is crazy? Then, is there any such function in PostgreSQL? With C it
would be so easy and fast.
Cheers!
Maxim Maletsky
-----Original Message-----
From: Hervé Piedvache [mailto:herve@elma.fr]
Sent: Friday, May 11, 2001 3:38 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Order by email address by domain ?
Hi,
I just want to order by a listing of email address by domain like :
toto@aol.com
tutu@aol.com
toto@be.com
tutu@be.com
toto@yahoo.com
Is it possible and how ?
Thanks !
--
Hervé Piedvache
Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve@elma.fr
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Order by email address by domain ?
@ 2001-05-11 14:35 Jeff Eckermann <jeckermann@verio.net>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Eckermann @ 2001-05-11 14:35 UTC (permalink / raw)
To: 'Frank Bax' <fbax@sympatico.ca>; pgsql-sql
???
I don't think anyone suggested adding a new builtin function.
Yes, your suggestion is good for an occasional use.
Where this kind of functionality is likely to be needed on a continuing
basis, my experience is that wrapping the code up in a custom function is
easier and cleaner than writing it out every time. Saves a lot of typing,
not to mention the risk of typos (which could give spurious results without
being obvious about it). Also, a function allows for indexing on that
value, which can be a great aid to performance.
I have found that sometimes it is better to just add a column or two to the
table to contain the needed key, because with large amounts of data that can
be much quicker. Doing this too much though can lead to a cluttered
database, and a loss of clarity about just what all of those extra fields
are for... It's a judgement call.
Just my $0.01 (That's $0.02 Australian :-))
> -----Original Message-----
> From: Frank Bax [SMTP:fbax@sympatico.ca]
> Sent: Thursday, May 10, 2001 8:53 PM
> To: pgsql-sql@postgresql.org
> Cc: herve@elma.fr
> Subject: Re: Order by email address by domain ?
>
> Why is everyone else suggesting new functions? This works (in 6.5.3):
>
> ORDER BY lower(substring(email from position('@' in email)+1 )),
> lower(email)
>
> remove the lower() functions if you don't need them (I had mixed case
> addresses).
>
> I am guessing/assuming that it's cheaper to just use entire email address
> in second key rather than extract before the '@' character.
>
> Frank
>
> At 08:37 PM 5/10/01 +0200, you wrote:
> >Hi,
> >
> >I just want to order by a listing of email address by domain like :
> >
> >toto@aol.com
> >tutu@aol.com
> >toto@be.com
> >tutu@be.com
> >toto@yahoo.com
> >
> >Is it possible and how ?
> >
> >Thanks !
> >--
> >Hervé Piedvache
> >
> >Elma Ingenierie Informatique
> >6, rue du Faubourg Saint-Honoré
> >F-75008 - Paris - France
> >http://www.elma.fr
> >Tel: +33-1-44949901
> >Fax: +33-1-44949902
> >Email: herve@elma.fr
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >http://www.postgresql.org/search.mpl
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-05-11 14:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-10 18:37 Order by email address by domain ? Hervé Piedvache <herve@elma.fr>
2001-05-10 19:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-05-11 01:53 ` Frank Bax <fbax@sympatico.ca>
2001-05-10 21:14 RE: Order by email address by domain ? Jeff Eckermann <jeckermann@verio.net>
2001-05-11 07:45 RE: Order by email address by domain ? Maxim Maletsky <maxim@japaninc.com>
2001-05-11 14:35 RE: Order by email address by domain ? Jeff Eckermann <jeckermann@verio.net>
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