create function isEmail(@value varchar(100))
returns bit
as
begin
declare @object int
declare @v bit
declare @str varchar(8000)
--javascript的正则表达式定义和调用语句
set @str='var reg=/^/w+[/+/./w-]*@([/w-]+/.)*/w+[/w-]*/.([a-z]{2,3}|/d+)$/i;reg.test("'+@value+'")'
--调用脚本组件来执行脚本
exec sp_OACreate 'MSScriptControl.ScriptControl',@object output
exec sp_OASetProperty @object, 'Language','javascript'
exec sp_OAMethod @object, 'eval', @v out,@str
--销毁com对象
exec sp_OADestroy @object
return @v
end
go
select * from(
select 'aa.bb.com' as email union
select 'aa@bb.com' union
select 'aa.bb@com' union
select 'aa@bb@com' ) a
where dbo.isEmail(email)=1