way to check email addresses

  • 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.

  • 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/.

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

     

  • 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 3 weeks, 2 days ago by  Tim-Hardy.
  • 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.

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

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