Sorting an E-mail column on Domain name

I have a 20,000+ Goldmine Database. I would like to sort the database by e-mail addresses. Not by what is before the "@" symbol. I want to sort by what's after the @symbol, i.e., @foo.com, @foobar.net... Can you give me a clue?

Here's the approach, illustrated with standard SQL. Use the POSITION string function to find the "@" in the e-mail address, and the SUBSTRING and LENGTH functions to pull out the rest of the string:

select emailaddress
     , substring (
         emailaddress
       from position('@' in emailaddress) + 1
       for length(emailaddress)
             - position('@' in emailaddress)
                 ) as domainname
  from goldminetable
order
    by domainname

I'm not familiar with the Goldmine database, so maybe it has its own non-standard syntax. For example, in Microsoft Access you have to say:

select emailaddress
     , mid (
         emailaddress
       , instr(emailaddress,'@') + 1
       , len(emailaddress)
             - instr(emailaddress,'@')
                 ) as domainname
  from goldminetable
order
    by mid (
         emailaddress
       , instr(emailaddress,'@') + 1
       , len(emailaddress)
             - instr(emailaddress,'@')
                 )

Some databases may not let you use the column alias "domainname" in the ORDER BY clause, so just repeat the function expression.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.