Skip to main content
Inspiring
May 18, 2006
Answered

Sort by Email Address

Hello,
I have an application that I want to sort by the email address URL ( everything right of the @ ) , but still want to display the full email address. There was a question similar to this but it was not answered in a format that I could use. I am using an Access Database.

My query is

<cfquery name="getEmails" dataSource="#application.db#">
SELECT email_ID, email_address
FROM email_out
ORDER by email_address
</cfquery>

I have tried the ListLast(email_address,"@") in the SQL statement but can not get it to work.
I have tried the ListLast(email_address,"@") in the query output which will print out the right part of the email address, but that is not quite what I want.
I want to have the query output all of the information but sort it by the text that is right of the @

i.e. janet@wpxdesign.com
jim@wpxdesign.com
wayne@wpxdesign.com

So basically if this did not throw an error this is what I would need -

<cfquery name="getEmails" dataSource="#application.db#">
SELECT email_ID, email_address
FROM email_out
ORDER by ListLast(email_address,"@")
</cfquery>

But this doesn't work I also tried the SELECT email_ID, ListLast(email_address,"@") as daEmail

but that didn't work either

Thanks in advance.
This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer
May 19, 2006
The code I posted works, I tested it.

Depending on your Access options, you may need to replace "@" with '@' though.

Replace the quotes and if it still fails, post the complete, unedited, error message.
You might also past the exact code you used.

Thanks,
-- MikeR

7 replies

jimWPXAuthor
Inspiring
May 20, 2006
That did it! Thank you so much.

Just out of curiosity ( And I now it's going to come up ) How would one accomplish this MYSQL?
Translate
May 20, 2006
quote:

Originally posted by: jimWPX
That did it! Thank you so much.

Just out of curiosity ( And I now it's going to come up ) How would one accomplish this MYSQL?


In later versions of MySql it should be:
ORDER BY
Substring (email_address, 1 + InStr (email_address, '@') )
, email_address


Translate
jimWPXAuthor
Inspiring
May 19, 2006
Tried the Mid function as posted and got an error

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Translate
Correct answer
May 19, 2006
The code I posted works, I tested it.

Depending on your Access options, you may need to replace "@" with '@' though.

Replace the quotes and if it still fails, post the complete, unedited, error message.
You might also past the exact code you used.

Thanks,
-- MikeR
Translate
May 19, 2006
In Access, you would use:

ORDER BY
Mid (email_address, 1 + InStr (1, email_address, "@" ,1) )
, email_address

Translate
jimWPXAuthor
Inspiring
May 19, 2006
Still doesn't work - the DB is Access
Translate
Inspiring
May 19, 2006
This depends on whether or not your db has enough string functions. Here is how it's done with redbrick. You'll have to read the manual for your own software to see if there is equivalent functionality.

order by
substr(email_address,
position('@', email_address) +1,
length(email_address) )
Translate