2008-11-18

Email addrees verification in SQL

The function is loosely based on the email validation function by Joseph Gama.

create function [dbo].[ufn_IsValidEmail](@email varchar(400))
--Returns 1 if the string is a valid email address 0 otherwise
--
-- The following assumptions are made:
-- 1. Level domain is 2, 3 or 4 characters long
-- 2. For the local part of email the most inclusive set of characters
-- is used. Adjust it as needed after NOT LIKE statement
-- in verify local section
-- 3. Though variable are properly allocated for all parts of email
-- actual verification of the length of email or its parts not done!
--
returns bit
as
BEGIN

DECLARE
@local VARCHAR(64), @domain VARCHAR(256),
@zone VARCHAR(64), @i int, @j int, @result bit
select @result=0
select @email = lower(@email)


if not @email like '_%@_%.__%' or
@email like '%..%' or
@email like '.%' or
@email like '%@.%' or
@email like '%.@%' or
@email like '%.' goto done

select @i=CHARINDEX('@',@email)
select @local=LEFT(@email,@i-1)

select @j = CHARINDEX('.',reverse(@email))
if @j < 3 goto done
select @zone=RIGHT(@email,@j - 1)
select @domain=substring(@email,@i+1,len(@email)-@i-@j)

-- verify zone
IF (LEN(@zone)=4) AND (@zone NOT LIKE '[a-z][a-z][a-z][a-z]')
GOTo done
IF (LEN(@zone)=3) AND (@zone NOT LIKE '[a-z][a-z][a-z]')
GOTo done
IF (LEN(@zone)=2) AND (@zone NOT LIKE '[a-z][a-z]')
GOTo done
select @i=1

-- verify local
WHILE (@i<LEN(@local))
BEGIN
IF SUBSTRING(@local,@i,1) NOT LIKE '[-''a-z0-9_.!#$%&*+/=?^_`{|}~]'
GOTo done
select @i=@i+1
END

-- verify domain
select @i=1
WHILE (@i<LEN(@domain))
BEGIN
IF SUBSTRING(@domain,@i,1) NOT LIKE '[-a-z0-9.]'
GOTo done
select @i=@i+1
END
-- All correct, return success
select @result = 1
done:
return @result
end

No comments: