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.
This was first published in November 2002