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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…