By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Query to Extract Distinct Substring after Specific Character & Provide Count Of Each

P: 2
Background: I am using ColdFusion to access an MS-Access database table via SQL.

I have a membership list database table with four email address fields (email1, email 2, email3, email4) - all in the same table. (Some folks have multiple email addresses, often from different Email Service Providers - ESPs.)

I need to develop an SQL query to produce a list of all the distinct ESPs (e.g. gmail.com, comcast.net, etc.) along with a count of how many instances of each there are, across all four of these fields.

The query should extract everything after the character "@" in each email address string. (The length of that substring will vary.)

As an example, the desired end result output should be a single list that looks something like:

comcast.net (6)
gmail.com (14)
cs.com (1)
hotmail.com (4)
att.net (12)
aol.com (2)
. . . .
etc.

Can this be done in a single query, or does it require multiple queries with the results of each somehow combined afterwards?

Any help would be greatly appreciated! Thanks!
Jun 28 '14 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.