Thread: Order by email address by domain ?

Order by email address by domain ?

From

Hervé Piedvache

Date:

10 May 2001, 18:12:31

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

Re: Order by email address by domain ?

From

Bruce Momjian

Date:

10 May 2001, 19:05:55

[ 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.comx@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,
Pennsylvania19026

RE: Order by email address by domain ?

From

Jeff Eckermann

Date:

10 May 2001, 20:39:20

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

Re: Order by email address by domain ?

From

Frank Bax

Date:

11 May 2001, 00:54:31

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
>

RE: Order by email address by domain ?

From

Maxim Maletsky

Date:

11 May 2001, 06:48:12

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

RE: Order by email address by domain ?

From

Jeff Eckermann

Date:

11 May 2001, 16:05:43

???
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