包含符号 @ . 且 @ 的位置在 . 的位置之前.select * from tb where charindex('@' , email) > 0 and charindex('.' , email) > 0 and charindex('@' , email) < charindex('.' , email) > 0
建立表是,创建CHECK约束怎么写?谢谢!
表设计->新建约束->复制dawugui(潇洒老乌龟) ( ) 的过去就OK 了
if object_id('fnCheckEmail') is not null drop function fnCheckEmail GO ----创建验证函数,返回值为1表示正确,否则格式错误 create function fnCheckEmail(@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 endGO ----创建测试数据 declare @t table(email varchar(1000)) insert @t select '[email protected]' union all select 'ab@[email protected]' union all select '@[email protected]' union all select 'abcxyz.com@' union all select '.abcxyz@com' union all select 'abc@xyz@com.' union all select 'ab.c@xyzcom' union all select '[email protected]' union all select '[email protected]' union all /*格式正确*/ select 'ab c@xyzcom' ----验证 select *,case dbo.fnCheckEmail(email) when 1 then '正确' else '错误' end from @t----清除测试环境 drop function fnCheckEmail
程序中好判断。就是想知道数据库中怎么弄。 我把这个弄进CHECK 约束,但是有错误。 charindex('@',email) > 0 and charindex('.',email) > 0 and charindex('@',email) < charindex('.',email) > 0 不知道怎么弄?
不知道是楼主写错了还是咋的 charindex('@',email) > 0 and charindex('.',email) > 0 and charindex('@',email) < charindex('.',email) > 0 这句有语法错误
charindex('@',email) > 0 and charindex('.',email) > 0 and charindex('@',email) < charindex('.',email) ??> 0orcharindex('@',email) > 0 and charindex('.',email) > 0 and charindex('@',email) < ?? ?? charindex('.',email) > 0
且 @ 的位置在 . 的位置之前.select * from tb where charindex('@' , email) > 0 and charindex('.' , email) > 0 and
charindex('@' , email) < charindex('.' , email) > 0
drop function fnCheckEmail
GO
----创建验证函数,返回值为1表示正确,否则格式错误
create function fnCheckEmail(@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
endGO
----创建测试数据
declare @t table(email varchar(1000))
insert @t
select '[email protected]' union all
select 'ab@[email protected]' union all
select '@[email protected]' union all
select 'abcxyz.com@' union all
select '.abcxyz@com' union all
select 'abc@xyz@com.' union all
select 'ab.c@xyzcom' union all
select '[email protected]' union all
select '[email protected]' union all /*格式正确*/
select 'ab c@xyzcom'
----验证
select *,case dbo.fnCheckEmail(email) when 1 then '正确' else '错误' end from @t----清除测试环境
drop function fnCheckEmail
我把这个弄进CHECK 约束,但是有错误。
charindex('@',email) > 0 and charindex('.',email) > 0 and
charindex('@',email) < charindex('.',email) > 0
不知道怎么弄?
charindex('@',email) > 0 and charindex('.',email) > 0 and
charindex('@',email) < charindex('.',email) > 0
这句有语法错误
and charindex('.',email) > 0
and charindex('@',email) < charindex('.',email)
??> 0orcharindex('@',email) > 0
and charindex('.',email) > 0
and charindex('@',email) < ??
?? charindex('.',email) > 0
不如在前台吧
比如说特殊字符的判断,_,-,可以有多个.,……挺麻烦