way to check email addresses

Question

way to check email addresses

cphite

SSCarpal Tunnel

Points: 4278
More actions
April 1, 2022 at 12:58 pm

#4009297

We have a process that sends emails to various customers whenever a certain event happens. The issue is that the folks who create the customer profiles sometimes mistype email addresses, adding a comma for example.
I am able to bypass these via putting a TRY/CATCH around the sp_send_dbmail command, and it event notifies us when a bad address is found so it can be fixed... however, I'd like to be a little more proactive and identify these beforehand. However, I don't want to just start sending mass emails to customers in the process.
Does anyone know of a way (existing function, or script they've used) to check the validity of an email address in T-SQL without calling sp_send_dbmail? Basically I'd just like to create a list each month that I can send to our data entry folks, of addresses they need to fix.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

I'm not aware of any TSQL functions, since it involves nslookup, telnet & SMTP EHLO commands. You can certainly do email address syntax validation in TSQL, but that doesn't guarantee the email address actually exists & receives email.

This article provides a good description of the process -- used partially or in whole by services that verify email addresses -- to do so (starting, of course, with verifying that the address meets the syntax requirements for a valid email address): https://mailtrap.io/blog/verify-email-address-without-sending/.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 2

Thanks for the link, that's very useful information.

Tim-Hardy Grasshopper Points: 12 More actions · Answer 3

Hey,

The previous response is right. T-SQL doesn't have built-in functionality to verify email deliverability definitively. However, you can achieve a lot with basic syntax validation and some additional considerations:

1. T-SQL Function for Basic Syntax Validation:

Here's a simple T-SQL function that checks basic email address format:

CREATE FUNCTION IsValidEmailSyntax (email VARCHAR(255))
RETURNS BIT
AS
BEGIN
DECLARE 
atPos INT, 
dotPos INT;

-- Check for "@" symbol
SET atPos = PATINDEX('%@%', email);
IF atPos = 0 OR atPos = 1 OR atPos = LEN(email) 
RETURN 0; -- No "@" or in wrong position

-- Check for dot after "@"
SET dotPos = PATINDEX('%.%%', email);
IF dotPos = 0 OR dotPos < atPos OR dotPos = LEN(email) 
RETURN 0; -- No dot after "@" or in wrong position

RETURN 1; -- Basic format seems valid
END;

2. Beyond Syntax: Additional Considerations

While this function helps with basic syntax, it doesn't guarantee deliverability. Here are some additional checks to consider:

Length Restrictions: Most email addresses have a limit (around 254 characters).
Reserved Characters: Some characters (like spaces, square brackets, etc.) are not allowed in email addresses.
Domain MX record check (optional): Check if the email domain has a valid MX record (indicates it receives email). This might require additional libraries or services.

3. Email Verification Services

For a more comprehensive and automated approach, consider exploring bulk email verification services. These services can handle various checks and significantly improve your email deliverability rates.

By combining T-SQL syntax validation with a reliable bulk verification service, you can effectively identify invalid email addresses before sending emails.

Feel free to share any specific questions you have about bulk verification services, and I'd be happy to help!

This reply was modified 1 month, 1 week ago by Tim-Hardy.

kaj SSCommitted Points: 1603 More actions · Answer 4

A different T-SQL function to check for valid email format is shown here:

https://www.mssqltips.com/sqlservertip/6519/valid-email-address-check-with-tsql/

I have also found that a simple select filter like

WHERE EmailAddress LIKE '%_@_%_.__%';

while not perfect, will go a long way towards ensuring that only valid email addresses are selected.