[SQL Server] Email validation function (using regular expressions)

[SQL Server] Email validation function (using regular expressions)

Create a user-defined function

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

CREATE FUNCTION [dbo].UFN_ValidEmailCheck ( @EMail VARCHAR(255) ) RETURNS BIT AS BEGIN DECLARE @IsValid BIT SET @IsValid = 0 IF @EMail IS NOT NULL BEGIN SET @EMail = LOWER(@EMail) IF @EMail like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%' AND @EMail NOT like '%@%@%' AND CHARINDEX('.@',@EMail) = 0 AND CHARINDEX('..',@EMail) = 0 AND CHARINDEX(',',@EMail) = 0 AND RIGHT(@EMail,1) between 'a' AND 'z' BEGIN SET @IsValid = 1 END END RETURN @IsValid END

Test 01.

1	SELECT dbo.UFN_ValidEmailCheck('wwwwaaaa@aaabbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 02.

1	SELECT dbo.UFN_ValidEmailCheck('^!afvb@bbbbb.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)

Test 03.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 04.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@.aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)