DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to add a check constraint for email verification?

Execute the following Microsoft T-SQL example script in SSMS Query Editor to add a new check constraint to the Person table WITH CHECK in the tempdb database:

USE tempdb;

GO

-- SQL create table with select into

SELECT TOP 10 ContactID, FirstName, LastName, Email=EmailAddress

INTO Person

FROM AdventureWorks.Person.Contact

GO

 

-- SQL check constraint - SQL alter table

-- SQL with check - check existing data for constraint

ALTER TABLE [dbo].[Person]

WITH CHECK

ADD CONSTRAINT [EmailCheck] CHECK

(

      CHARINDEX('.',[Email],CHARINDEX('@',[Email]))-CHARINDEX('@',[Email])>1

      AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) > 2

      AND CHARINDEX('@',LTRIM([Email])) > 2

)

GO

 

-- SQL check constraint violated missing . (period)

INSERT Person (FirstName, LastName, Email)

VALUES ('Celine', 'Dion', '[email protected]')

GO

/*

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "EmailCheck".

The conflict occurred in database "tempdb", table "dbo.Person", column 'Email'.

The statement has been terminated.

 

*/

 

-- SQL check constraint met

INSERT Person (FirstName, LastName, Email)

VALUES ('Celine', 'Dion', '[email protected]')

GO

-- (1 row(s) affected)

 

SELECT * FROM Person ORDER BY Email

GO

/* Partial result

 

ContactID   FirstName   LastName    Email

8           Carla       Adams       [email protected]

2           Catherine   Abel        [email protected]

12          Celine      Dion        [email protected]

6           Frances     Adams       [email protected]

1           Gustavo     Achong      [email protected]

*/

 

-- Cleanup

DROP TABLE Person

GO

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE