E-Mail Validator

Sean Smith, 2019-04-15 (first published: 2009-11-18)

Simply pass a field which contains an e-mail address to the function and either a 1 (valid) or 0 (invalid) will be returned as output.

This routine is not meant to be an all-encompassing solution for identifying invalid e-mail addresses, but rather a quick / easy check routine to catch obvious errors and mistakes.

Examples of how to run the function:

SELECT
 dbo.ufn_E_Mail_Validator ('me@nowhere.com') AS is_valid_yes
,dbo.ufn_E_Mail_Validator ('me@@nowhere.com') AS is_valid_no

Or... against a table, returning only invalid e-mail addresses (NULL values are considered valid by the function as they are essentially "unknown"):

SELECT
 CC.e_mail
,dbo.ufn_E_Mail_Validator (CC.e_mail) AS is_valid
FROM
dbo.company_contact CC
WHERE
dbo.ufn_E_Mail_Validator (CC.e_mail) = 0

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON ---------------------------------------------------------------------------------------------------------------------- --Error Trapping: Check If Function Already Exists And Drop If Applicable ---------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID ('[dbo].[ufn_E_Mail_Validator]') IS NOT NULL BEGIN DROP FUNCTION [dbo].[ufn_E_Mail_Validator] END GO ---------------------------------------------------------------------------------------------------------------------- --Function Details: Listing Of Standard Details Related To The Function ---------------------------------------------------------------------------------------------------------------------- -- Purpose: Basic E-Mail Address Validation Routine -- Create Date: 11/18/2009 -- Created By: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com) -- Modifications: NONE ---------------------------------------------------------------------------------------------------------------------- --Main Query: Create Function ---------------------------------------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[ufn_E_Mail_Validator] (@vParameter AS VARCHAR (8000)) RETURNS BIT AS BEGIN DECLARE @vParameter_Length AS SMALLINT DECLARE @vIs_Valid AS BIT DECLARE @vPosition_Current AS SMALLINT DECLARE @vCharacter_Current_ASCII AS TINYINT SET @vParameter = LOWER (LTRIM (RTRIM (@vParameter))) SET @vParameter_Length = LEN (@vParameter) SET @vIs_Valid = 1 IF @vParameter_Length IS NULL BEGIN GOTO skip_query END IF (@vParameter_Length = 0 OR CHARINDEX ('@', @vParameter) < 2 OR CHARINDEX ('.', @vParameter) < 2 OR CHARINDEX ('.@', @vParameter) <> 0 OR CHARINDEX ('..', @vParameter) <> 0 OR CHARINDEX ('.', REVERSE (@vParameter)) < 3 OR CHARINDEX ('.', @vParameter, CHARINDEX ('@',@vParameter)) = 0 OR RIGHT (@vParameter,1) = '@' OR @vParameter LIKE '%@%@%') BEGIN SET @vIs_Valid = 0 GOTO skip_query END SET @vPosition_Current = 1 SET @vCharacter_Current_ASCII = ASCII (LEFT (@vParameter,1)) WHILE @vPosition_Current <= @vParameter_Length BEGIN IF @vCharacter_Current_ASCII NOT IN (43,45,46,64,95) AND @vCharacter_Current_ASCII NOT BETWEEN 48 AND 57 AND @vCharacter_Current_ASCII NOT BETWEEN 97 AND 122 BEGIN SET @vIs_Valid = 0 GOTO skip_query END SET @vPosition_Current = @vPosition_Current+1 SET @vCharacter_Current_ASCII = ASCII (SUBSTRING (@vParameter, @vPosition_Current, 1)) END skip_query: RETURN @vIs_Valid END