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', 'celinedion@sympaticoca')

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', 'celinedion@sympatico.ca')

GO

-- (1 row(s) affected)

 

SELECT * FROM Person ORDER BY Email

GO

/* Partial result

 

ContactID   FirstName   LastName    Email

8           Carla       Adams       carla0@adventure-works.com

2           Catherine   Abel        catherine0@adventure-works.com

12          Celine      Dion        celinedion@sympatico.ca

6           Frances     Adams       frances0@adventure-works.com

1           Gustavo     Achong      gustavo0@adventure-works.com

*/

 

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