SQL Stored Procedure getting list of email addresses

Basically I need to extract a list of distinct email addresses and their count of how many times they are used, in the list

 the message fields look like this "Vc (VC Email - Dev)" <vcdev@q88.com>, vcdev@awtworldwide.com

...delimited by a comma, sometimes with friendly name sometimes not, any help would be appreciated.

  SELECT a.to_msg,
         a.cc_msg,
         b.FolderID
  FROM tblawm_messages a
  JOIN tblEmailFile b ON a.MessageId = b.MessageId and b.IsMatch = 1
  WHERE a.VcAccountId = 9
  ORDER BY b.FolderID

Open in new window

Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Can you please provide complete table structure e some sample data?
0
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Basically there's a message table tblawm_messages  with a messageID and a to_msg field which can contain a list of email addresses. Some can possibly have friendly names

like
MessageID   to_msg
1                     "Vc (VC Email - Dev)" <vcdev@q88.com>, vcdev@awtworldwide.com
2                     "Vc (VC Email - Dev)" <vcdev@q88.com>, martin@awtworldwide.com, "amy" <amy@q88.com>
3                      "Richard" <Richard@q88.com>, <amy@q88.com>



 I'm trying to go thru the whole message table to write a query to accomplish
A. Get all distinct email addresses into one column
B. Get a count of how times time each of those email addresses appeared into another column
0
Brian CroweDatabase AdministratorCommented:
You will need to substitute a split function of your own but you can find dozens of examples with a simple google search.  I would recommend putting the logic below in a table valued function and using CROSS APPLY.

DECLARE @Sample	VARCHAR(MAX) = 'Vc (VC Email - Dev)" <vcdev@q88.com>, vcdev@awtworldwide.com'

SELECT DISTINCT
	CASE
		WHEN CHARINDEX('<', Value) = 0 THEN LTRIM(RTRIM(Value))
		ELSE SUBSTRING(Value, CHARINDEX('<', Value) + 1, CHARINDEX('>', Value, CHARINDEX('<', Value)) - CHARINDEX('<', Value) - 1)
	END AS Email
FROM dbo.SplitText(@Sample, ',')

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Excellent that got me that far,
  SELECT a.MessageId,
         a.to_msg,
         c.Value,
         b.FolderID
  FROM [dbo].[tblawm_messages] a
  JOIN [dbo].[tblEmailFile] b ON a.MessageId = b.MessageId and b.IsMatch = 1
  OUTER APPLY [dbo].[udtfSplit](a.to_msg,',') c
  WHERE a.VcAccountId = 9

Open in new window

, now how do I make the list distinct while simultaneously getting the counts

like

EmailAddress            Count
 <vcdev@q88.com>     3
<amy@q88.com>        4
0
Brian CroweDatabase AdministratorCommented:
SELECT a.MessageId,
         a.to_msg,
         c.Value,
         b.FolderID
   COUNT(a.MessageId) AS EmailCount
  FROM [dbo].[tblawm_messages] a
  JOIN [dbo].[tblEmailFile] b ON a.MessageId = b.MessageId and b.IsMatch = 1
  OUTER APPLY [dbo].[udtfSplit](a.to_msg,',') c
  WHERE a.VcAccountId = 9
GROUP BY a.MessageId,
         a.to_msg,
         c.Value,
         b.FolderID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you just what I needed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.