70-433 chapter 3, regular expressions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

70-433 chapter 3, regular expressions

Author

Topic

john101sql
Starting Member

4 Posts

Posted - 2012-04-01 : 13:07:02

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
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-01 : 13:37:48

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

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-04-01 : 16:06:46

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.

john101sql
Starting Member

4 Posts

Posted - 2012-04-01 : 16:44:08

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-04-01 : 17:23:34

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources