SQL Function to identify invalid email addresses in the database

When you are manipulating large quantities of data, you might have some records containing email addresses which slip through as invalid.

Following function checks these conditions:

No embedded spaces
‘@’ can’t be the first character of an email address
‘.’ can’t be the last character of an email address
There must be a ‘.’ somewhere after ‘@’
the ‘@’ sign is allowed
Domain name should end with at least 2 character extension
can’t have patterns like ‘.@’ and ‘..’

--print dbo.fnc_IsValidEmail('aaa@aaa.com')
--print dbo.fnc_isValidEmail('Mi email@test.com')
CREATE FUNCTION fnc_IsValidEmail
(@Email_Address varchar(255))
RETURNS BIT
BEGIN
DECLARE @Valid bit
SET @Valid = 0
if @Email_Address=''
SET @Valid = 1
ELSE

IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
SET @Valid = 1

RETURN @Valid
END

How to call:

SELECT Email, dbo.fnc_IsValidEmail(Email) as Valid
from tblSuppliers
WHERE Email”

Capture

Advertisements