Hi,
On Fri, 2002-05-03 at 02:43, Andrew Kuebler wrote:
> I use Perl/DBI with MySQL and I'm trying to find a way to sort email
> addresses by domain. I would assume there is probably an option in MySQL
> to strip off the text after the '@' sign that will allow me to sort on.
>
> In Perl:
>
> @fields = split(/@/, $email);
> My domain would be displayed as: $fields[1]
>
> Is there a comparable way to do this in MySQL? I was looking in the
> substring area, but I really need a split option.
Using a function in an ORDER BY clause is not very fast, as the server
will need to run that function lots of times for different rows.
Alternatively, you could store the user & domain parts in separate
columns. This can be space-inefficient though (or use VARCHAR fields,
which can be slower).
But...
A very nice solution with domain names is to store the "[EMAIL PROTECTED]"
with all characters reversed. You can do this by using the REVERSE()
function.
When INSERTing, you'd store REVERSE("[EMAIL PROTECTED]").
In the database, this would be "moc.niamod@esu" ;-) but who cares how
it's stored, as long as your retrieval operations work and are fast!
You can do a plain ORDER BY directly on this field, no functions
required. So it's fast. In the column specification of the SELECT, you
just use REVERSE(colname) again to get back the proper order.
An additional benefit is that you also get subdomains (bla.domain.com)
ordered right next to the primary domain.
It's a neat trick with minimal effort. ;-)
Hope this helps!
Regards,
Arjen.
--
MySQL Training, Support, Licenses @ https://order.mysql.com/?marl
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer
/_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
