如何写SQL CHECK 约束表达式来约束一个完整的邮箱格式

Question

create table tb
(
  id int,
  email varchar(50),
  CONSTRAINT chk_email CHECK (charindex('@163.',email)>0 or charindex('@gmail.',email)>0 or charindex('@QQ.',email)>0 or charindex('@yahoo.',email)>0)
)insert into tb values(1,'abc')
insert into tb values(2,'[email protected]')select * from tbdrop table tb/**
服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'chk_email' 冲突。该冲突发生于数据库 'testdb'，表 'tb', column 'email'。
语句已终止。（所影响的行数为 1 行）id          email                                              
----------- -------------------------------------------------- 
2           [email protected]（所影响的行数为 1 行）
**/
邮箱类型是指什么？

Answer 1

create table tb
(
  id int,
  email varchar(50),
  CONSTRAINT chk_email CHECK (charindex('@163.',email)>1 or charindex('@gmail.',email)>1 or charindex('@QQ.',email)>1 or charindex('@yahoo.',email)>1)
)用>1可以保证不能以@开头

Answer 2

CREATE  FUNCTION F_EMAIL
(
@EMAIL VARCHAR(50)
)
RETURNS INT  --返回1是正确；返回0是错误
BEGIN
  DECLARE @VALUE INT,@LEN INT
  DECLARE @TMP VARCHAR(50)
  DECLARE @CHECK1 VARCHAR(50)
  DECLARE @CHECK2 VARCHAR(50)
  DECLARE @CHECK3 VARCHAR(50)
  SET @TMP=RTRIM(LTRIM(@EMAIL))
  SET @LEN=LEN(@TMP)
  IF @LEN-LEN(REPLACE(@TMP,'@',''))=1 AND @LEN-LEN(REPLACE(@TMP,'.',''))>=1 AND CHARINDEX('@',@TMP)<>1
  BEGIN
    SET @CHECK1=LEFT(@TMP,CHARINDEX('@',@TMP)-1)
    SET @CHECK2=STUFF(@TMP,1,CHARINDEX('@',@TMP),'')
    SET @CHECK3=STUFF(@CHECK2,1,CHARINDEX('.',@CHECK2),'')
    SET @CHECK2=LEFT(@CHECK2,CHARINDEX('.',@CHECK2)-1)
    IF LEN(@CHECK1)>0 and LEN(@CHECK2)>0 AND LEN(@CHECK3)>0
    BEGIN  
      IF  PATINDEX('%[^a-zA-Z0-9._-]%',@CHECK1)>0
        SET @VALUE=0
      ELSE
      BEGIN
        IF  PATINDEX('%[^a-zA-Z0-9_-]%',@CHECK2)>0
          SET @VALUE=0
        ELSE
        BEGIN
          IF CHARINDEX(UPPER(@CHECK2),UPPER('gmail,QQ,163,sina,yahoo'))>0 
  BEGIN
            IF  PATINDEX('%[^a-zA-Z0-9._-]%',@CHECK3)>0
              SET @VALUE=0
            ELSE SET @VALUE=1
    END
          ELSE SET @VALUE=0
        END
      END
    END
    ELSE SET @VALUE=0 
  END
  ELSE SET @VALUE=0 
RETURN @VALUE
END--测试
create table tb
(
  id int identity,
  email varchar(50),
  CONSTRAINT chk_email CHECK (dbo.F_EMAIL(email)=1)
)
insert into tb SELECT '[email protected]'
insert into tb SELECT  '[email protected]'
insert into tb SELECT  '[email protected]'
insert into tb SELECT  '[email protected]'
insert into tb SELECT  'AS@[email protected]'/*
（所影响的行数为 1 行）服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'chk_email' 冲突。该冲突发生于数据库 'Test'，表 'tb', column 'email'。
语句已终止。（所影响的行数为 1 行）
（所影响的行数为 1 行）服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN CHECK 约束 'chk_email' 冲突。该冲突发生于数据库 'Test'，表 'tb', column 'email'。
语句已终止。*/SELECT * FROM TB/*
id          email                                              
----------- -------------------------------------------------- 
1           [email protected]
3           [email protected]
4           [email protected]（所影响的行数为 3 行）
*/