• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Sort by Email Address

Participant ,
May 18, 2006 May 18, 2006

Copy link to clipboard

Copied

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.
TOPICS
Advanced techniques

Views

497

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Advisor , May 19, 2006 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

Votes

Translate

Translate
LEGEND ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

Still doesn't work - the DB is Access

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

In Access, you would use:

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
May 19, 2006 May 19, 2006

Copy link to clipboard

Copied

LATEST
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


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation