SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 70-433 chapter 3, regular expressions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

john101sql
Starting Member

United Kingdom
4 Posts

Posted - 04/01/2012 :  13:07:02  Show Profile  Reply with Quote
apologies if this has been posted before but I couldn't find the same issue.

The answer to one of the excercises is this chapter is (the of this constraint is to prevent Name field containing a name with less than 3 characters)

ALTER TABLE Test.Customers
ADD CONSTRAINT CKCustomersName
CHECK(Name LIKE N'[A-Z]__%');

SQL server 2008

But I don't know why there's an 'N' just after LIKE. Should it be this

ALTER TABLE Test.Customers
ADD CONSTRAINT CKCustomersName
CHECK(Name LIKE '[A-Z]__%');

But I don't know if N' has a special meaning few examples on this microsoft page.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Thanks in advance

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/01/2012 :  13:37:48  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
N before a string literal indicates that it's a unicode string. If the column name is unicode (nchar or nvarchar) then the string in the check constraint should be unicode to avoid implicit conversion problems.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 04/01/2012 :  16:06:46  Show Profile  Reply with Quote
I'm confused...just out of curiosity, if you wanted to ensure a specified length or minimum length, wouldn't it make more sense to do this:

ALTER TABLE Test.Customers
ADD CONSTRAINT CKCustomersName
CHECK(LEN(Name)>2);

I know that wasn't your question, it just seems simpler to give a minimum length of input.
Go to Top of Page

john101sql
Starting Member

United Kingdom
4 Posts

Posted - 04/01/2012 :  16:44:08  Show Profile  Reply with Quote
I struggle to create a simple constraint. The example I gave is from the 70-433 book. In the book it says that this constraint should verify Name starts with a letter and be at least 3 characters long.
Name is declared like this

Name NVARCHAR(50) NOT NULL

Edited by - john101sql on 04/01/2012 16:44:42
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/01/2012 :  17:23:34  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
So the name is a unicode column and hence the string in the check constraint should be unicode as well, which is what that N means.

If the column was defined as VARCHAR(50), then the check constraint could be CHECK(Name LIKE '[A-Z]__%')

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000