SQL to split comma delimited email string into friendly names and email addresses

I am running into an issue with comma delimited strings within an string of email addresses. Problem being the friendy names can also have commas in them, is there some clever way that I can do this? Below is what I have so far

DECLARE @Email NVARCHAR(MAX) = '"John Smith" john@yahoo.com, "Larry, Moe, Curly" stooges@gmail.com'
SELECT EmailAddress=REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(c.Value,CHARINDEX('"',c.Value,2) + 1,LEN(c.Value) - CHARINDEX('"',c.Value,2)))),'<',''),'>',''),'''',''),
       FriendlyName=REPLACE(LTRIM(RTRIM((CASE WHEN CHARINDEX('"',c.Value,2) = 0 THEN '' ELSE SUBSTRING(c.Value,1,CHARINDEX('"',c.Value,2) + 1) END))),'''','')
FROM [dbo].[udtfSplit](@Email,',') c

Open in new window

Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Brian CroweDatabase AdministratorCommented:
Use the double quotes to split the string.  If there is an "@" in the string than it is the email address otherwise it is the friendly name.  I would recommend using a split function and then wrap that in another table-valued function to return the EmailAddress and FriendlyName.
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
I cant rely on the quotes on being in there in all cases...some emails in the string won't have friendly names just the emails address and some will
Scott PletcherSenior DBACommented:
Yes, the "clever" way is to separate email addresses with a semicolon, not a comma, as is standard practice, for example, when using sp_send_dbmail.

Other than that, you might be best off just to write your own left-to-right splitter than correctly handled values within double quotes.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
I understand that but based on your example, all friendly names are encapsulated in double-quotes, which means that everything outside of those quotes is an email address with a few extraneous spaces and possibly a comma.  I took your example and split it by double-quotes below.  If the split value contains "@" then it is an email address after LTRIM, RTRIM, and removing any commas.  The query to convert a table like this into your desired output isn't too difficult.  You add a column indicating whether it is or is not an email address with a simple CHARINDEX call, add a row_number value or better yet use a split function that returns the index value within the original string, and then you self join to put the friendly name and email address together.

John Smith
Larry, Moe, Curly

This is why most email systems use semi-colons to delimit recipient lists :-)
Brian CroweDatabase AdministratorCommented:
try this...you will need a split function but you can find those all over the internet with a simple google search.  This one requires that it return some sort of value that indicates it's order in the string but that is also fairly common.

DECLARE @Email NVARCHAR(MAX) = '"John Smith" john@yahoo.com, "Larry, Moe, Curly" stooges@gmail.com, someone@someemail.com, mickey@disney.com, minnie@disney.com, "Huey, Dewey, Louey" donaldnephews@disney.com';

WITH cteSplit AS
	SELECT ISNULL(List2.Value, List1.Value) AS String,
		CASE WHEN CHARINDEX('@', List1.Value) > 0 THEN 1 ELSE 0 END AS IsEmailAddress,
		ROW_NUMBER() OVER(ORDER BY List1.Position, List2.Position) AS RowNumber
	FROM dbo.SplitText(@Email, '"') AS List1
		SELECT Value, 1 AS IsEmailAddress, Position
		FROM dbo.SplitText(List1.Value, ',')
		WHERE CHARINDEX('@', List1.Value) > 0
	) AS List2
SELECT cteEmail.String AS EmailAddress,
	cteFriendly.String AS FriendlyName
FROM cteSplit AS cteEmail
LEFT OUTER JOIN cteSplit AS cteFriendly
	ON cteEmail.RowNumber = cteFriendly.RowNumber + 1
	AND cteFriendly.IsEmailAddress = 0
WHERE cteEmail.IsEmailAddress = 1

Open in new window

Scott PletcherSenior DBACommented:
Here's a function that will convert the commas that "should" be semicolons to semicolons.  That is:
"John Smith" john@yahoo.com, "Larry, Moe, Curly" stooges@gmail.com, someone@someemail.com, mickey@disney.com, minnie@disney.com, "Huey, Dewey, Louey" donaldnephews@disney.com
gets changed to:
"John Smith" john@yahoo.com; "Larry, Moe, Curly" stooges@gmail.com; someone@someemail.com; mickey@disney.com; minnie@disney.com; "Huey, Dewey, Louey" donaldnephews@disney.com

Then you can do the "standard" email split on semicolon and process the emails normally.  Later, if/when the emails are actually entered with a semicolon as the delimiter, simply remove the call to this function and the rest of the code should continue to work fine.

CREATE FUNCTION dbo.DelimitEmailAddresses (
    @emails nvarchar(max)
RETURNS nvarchar(max)
DECLARE @byte int
SET @byte = PATINDEX('%[^ ]%', @emails)
WHILE @byte < LEN(@emails)
    IF SUBSTRING(@emails, @byte, 1) = '"'
        SET @byte = CHARINDEX('"', @emails, @byte + 1) + 1
        IF @byte = 1 --ending " is missing
    END --IF
    SET @byte = CHARINDEX(',', @emails, @byte)
    IF @byte = 0
    SET @emails = STUFF(@emails, @byte, 1, ';')
    SET @byte = @byte + 1
    WHILE SUBSTRING(@emails, @byte, 1) = ' '
    AND @byte <= LEN(@emails)
        SET @byte = @byte + 1
RETURN @emails

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
Doug BishopDatabase DeveloperCommented:
When you build the list to begin with, can you change the delimiter so that you have:
DECLARE @Email NVARCHAR(MAX) = '"John Smith" john@yahoo.com|"Larry, Moe, Curly" stooges@gmail.com'
Then call the function with
FROM [dbo].[udtfSplit](@Email,'|')
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thanks guys, I ultimately went with Scott's solution but you were all brilliant. Maybe I can find al way to alter to commas further up the process chain at a later time.
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

From novice to tech pro — start learning today.