if object_id('fCheckEmail') is not null
drop function fCheckEmail
GO
----创建验证函数,返回值为1表示正确,否则格式错误
create function fCheckEmail(@Email varchar(1000))
returns bit
as
begin
declare @rtv bit
if
charindex(' ',@email) > 0 or /*含有空格*/
len(@email) - len(replace(@email,'.','')) > 1 or /*'.'超过1个*/
len(@email) - len(replace(@email,'@','')) > 1 or /*'@'超过1个*/
right(@email,1) = '.' or /*以'.'结尾*/
right(@email,1) = '@' or /*以'@'结尾*/
left(@email,1) = '.' or /*以'.'开头*/
left(@email,1) = '@' or /*以'.'开头*/
charindex('.',@email)-charindex('@',@email) < 0 or /*'.'在'@'前面*/
charindex('@',@email)-charindex('.',@email) = 1 or /*'@'与'.'相邻*/
charindex('.',@email)-charindex('@',@email) = 1 /*'@'与'.'相邻*/
set @rtv = 0
else
set @rtv = 1
return @rtv
end

GO
/*仅供参考,可以灵活添加\删除或修改某些条件*/