--
验证
email
函数
select 'junkaih@163.com'
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
--
测试语句
select
* from(
select 'aa.bb.com' as email union
)
as T where dbo.isEmail(email)=1
--
如果
SQL2005
提示没有权限则打开
Ole Automation Procedures
--exec sp_configure 'show advanced options',1
--reconfigure
--exec sp_configure 'Ole Automation Procedures',1
--reconfigure