Validate email address will be accepted by sp_send_dbmail?

Question

How do you validate that a text string is a valid email address which will be accepted by the sp_send_dbmail function?

I've looked through other questions like this one, which yes works great, until a user copies their email address from outlook and comes through like Jane Doe <DoeJ@xdomain.com>, which fails to send via the system proc.

I also want users to be able to supply multiple emails in a single string separated by semicolons, which are accepted by sp_send_dbmail. Thanks!

score 1 · Accepted Answer · answered Mar 05 '21 at 12:01

You can try this (there are other ways),

--PARAM:START
DECLARE @EmailList varchar(max);

SET @EmailList = 'Jane Doe <DoeJ@xdomain.com>;info.support@mssqltips.com;.info@mssqltips.com;
info..@mssqltips.com;info@mssqltips.c;info@support@mssqltips.com;info.support@mssql_tips.com;
+info@mssqltips.com;info Support@mssqltips.com;info@mssql tips.com;NULL;22@mssqltips.com;@mssqltips.com';
--PARAM:END

--VALIDATION:START
DECLARE @EmailTable TABLE
(
    Email varchar(max),
    IsValid bit
);

INSERT INTO @EmailTable(Email, IsValid)
SELECT LTRIM(RTRIM(value))
,CASE WHEN LTRIM(RTRIM(value)) = '' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '% %' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE ('%["(),:;<>\]%') THEN 0
        WHEN SUBSTRING(LTRIM(RTRIM(value)),CHARINDEX('@',LTRIM(RTRIM(value))),LEN(LTRIM(RTRIM(value)))) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 0
        WHEN (LEFT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]') OR RIGHT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]')) THEN 0                                                                                    
        WHEN (LTRIM(RTRIM(value)) LIKE '%[%' or LTRIM(RTRIM(value)) LIKE '%]%') THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '%@%@%' THEN 0
        WHEN LTRIM(RTRIM(value)) NOT LIKE '_%@_%._%' THEN 0
        ELSE 1 
    END
FROM STRING_SPLIT(@EmailList, ';');
--VALIDATION:END

--GET VALID EMAIL
DECLARE @ValidEmailList varchar(max);

SELECT @ValidEmailList = COALESCE(@ValidEmailList + ', ', '') + Email
FROM @EmailTable
WHERE IsValid = 1;

--DO SENDING OF EMAIL USING ValidEmailList

Source: Valid Email Address Check with TSQL

Compatibility level 110, though. I can't change that (and DBA's won't allow it to be changed). — dya, Mar 09 '21 at 05:01
Heh, I found a way. Your solution appears to work great, thank you! — dya, Mar 09 '21 at 05:16

score 1 · Answer 2 · answered Mar 09 '21 at 05:47

@tontonsevilla Great solution, I just modified it a bit to remove the intermediate table and replace any line breaks that may show up (as they did from copying your solution):

DECLARE @EmailList VARCHAR(MAX);

SET @EmailList
    = 'Jane Doe <DoeJ@xdomain.com>;   info.support@mssqltips.com;.info@mssqltips.com;
info..@mssqltips.com;info@mssqltips.c;info@support@mssqltips.com;info.support@mssql_tips.com;
+info@mssqltips.com;info Support@mssqltips.com;info@mssql tips.com;NULL;22@mssqltips.com;@mssqltips.com';

DECLARE @ValidEmailList VARCHAR(MAX);

SELECT  @ValidEmailList = COALESCE(@ValidEmailList + ';', '') + [x].[Email]
FROM    (
            SELECT  TRIM(REPLACE(REPLACE([value], CHAR(10), ''), CHAR(13), '')) [Email]
            FROM    STRING_SPLIT(@EmailList, ';')
            WHERE   LEN([value]) > 4
                    AND CHARINDEX(' ', TRIM([value])) = 0
                    AND TRIM([value])NOT LIKE ('%["(),:;<>\]%')
                    AND SUBSTRING(TRIM([value]), CHARINDEX('@', TRIM([value])), LEN(TRIM([value])))NOT LIKE ('%[!#$%&*+/=?^`_{|]%')
                    AND LEFT(TRIM([value]), 1)NOT LIKE ('[-_.+]')
                    AND RIGHT(TRIM([value]), 1)NOT LIKE ('[-_.+]')
                    AND TRIM([value])NOT LIKE '%[%'
                    AND TRIM([value])NOT LIKE '%]%'
                    AND TRIM([value])NOT LIKE '%@%@%'
                    AND TRIM([value]) LIKE '_%@_%._%'
        ) [x];

SELECT  @ValidEmailList;

Validate email address will be accepted by sp_send_dbmail?

2 Answers2