sql server - SQL constraint E-Mail

Question

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

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've tried some different things but cant seem to make it work. Also it should be noticed that I am a beginner.

Thanks for your help

Answer 1

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

If table not Exist

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 Rejected Inputs

some@some.!%#¤com

some@some.!%#com

some@some.%#com

some@some.#com

some@some@com

!some@some@com

etc...

Some Excepted Inputs

some@some.com

some222@some.com

harry_porter@some.com

332@some.com

etc....

If you have already a table then

 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