Обсуждение: 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
[ 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
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
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
>
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
???
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