SQL约束电子邮件 [英] SQL constraint E-Mail

问题描述

我有一个必须在SQL Server管理器中完成的任务。

i have an assignment that i have to do in SQL Server Manager.

我有一个数据库和一个名为 User的表。在用户下有一个名为 email 的列。

I have a database, and a table named User. Under user there are a column named e-mail.

任务是为该表创建一个新约束。会影响列电子邮件。

The assignment is to create a new constraint to that table that affect the column e-mail.

必须只有一个' @ '，至少一个'。'

There has to be only one '@' and minimum one '.'

不允许使用任何特殊字符，例如（！， ，＃，¤ ，％等）-不包括'@'和'。'

It is not allowed to be any special characters such as ( !, ", #, ¤, %, etc.) <- this do not include the '@' and '.'

I我尝试了一些不同的方法，但似乎无法使其正常工作。还应该注意，我是一个初学者。

I've tried some different things but cant seem to make it work. Also it should be noticed that I am a beginner.

感谢您的帮助

推荐答案

或者，您可以使用约束创建表，这样可以检查所有给定条件是否都满足。 b请使用您的表的数据更改

Alternatively you can create your table with constraint, like this which will check if all the given conditions satisfies. Please change table data with your's

如果表不存在

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Email  varchar(255),
CHECK (len(Email) - len(replace(Email,'@',''))=1 AND len(Email) - 
len(replace(Email,'.',''))=1 AND CHARINDEX('!',Email)!>0 AND 
CHARINDEX('#',Email)!>0 AND CHARINDEX('%',Email)!>0 AND 
CHARINDEX('¤',Email)!>0 AND CHARINDEX('"',Email)!>0)
);

某些拒绝的输入

some @ some。！％＃¤com

some@some.!%#¤com

some @ some。！％＃com

some@some.!%#com

some @ some。％＃com

some@some.%#com

some@some.#com

some@some.#com

some @ some @ com

some@some@com

！some @ some @ com

!some@some@com

等...

某些输入除外

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

等...。

如果您已经有表格，则

 GO

 ALTER TABLE [dbo].[Yourtablename]  WITH CHECK ADD Constraint EmailConstraint CHECK  (((len([Email])-
 len(replace([Email],'@','')))=(1) AND (len([Email])-
 len(replace([Email],'.','')))=(1) AND charindex('!',[Email])<=(0) AND 
 charindex('#',[Email])<=(0) AND charindex('%',[Email])<=(0) AND 
 charindex('¤',[Email])<=(0) AND charindex('"',[Email])<=(0)))
 GO

这篇关于SQL约束电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！