rickchild provided this function to check email syntax
CREATE FUNCTION EMAILVALIDATE (@email varChar(100))
RETURNS int
AS
BEGIN
DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos
int,@periodPos int
SET @valid = 1
SET @invalChars = ' /:,;'
--Check to see if it's blank
IF len(ltrim(rtrim(@email))) = 0
SET @valid = 0
ELSE
--Loop invalid characters to see if it exists in email
WHILE len(@invalChars) > 0
BEGIN
SET @badChar = substring(@invalChars,1,1)
IF(charindex(@badChar,@ema
il) > 0)
--If invalid character was found, return 0 to invalidate
SET @valid = 0
SET @invalChars = replace(@invalChars,@badCh
ar,'')
END
--Check to see if "@" exists.
SET @atPos = charindex('@',@email,1)
IF @atPos = 0
SET @valid = 0
--Check to see if extra "@" exists after 1st "@".
IF charindex('@',@email,@atPo
s+1) > 0
SET @valid = 0
SET @periodPos = charindex('.',@email,@atPo
s)
IF @periodPos = 0
SET @valid = 0
IF (@periodPos+3) > len(@email)
SET @valid = 0
RETURN (@valid)
END
It works but has 2 glitches
1. at the beginning it cehck for invalid characters and has a list ' /:,;'
It checks for these characters... but if á or ñ are in the email it give an ok answer. ideal to have a translation table or just report it as invalid.
Why not test for valid characters (the list is shorter than the possible invalid characters) of course you have to check all charactes where as when checkin for invalid characters as soon as you find one it is automatically invalid.
Other considerations_ the '_' is valid for the user part but not the domain and the '-' is valid for the domain but not the user
2. Towards the end it checks for a '.' to check for .com .net.org etc. it uses a 3 as parameter to all addresses with .mx .cl .co etc are reportes as invaled. I changed the 3 to a 2 and it seems to work...
A solution to the first part woul be welcome.
Start Free Trial
