SQL EXCEPT not recognizing differences in capitalization

I am comparing two tables to determine which rows have changed and should be included in a data interface.  Changes in capitalization are not being trapped by the EXCEPT clause. The following example does not return any rows when it should return the row from the left table because it is technically different.

Create table #temp3 (name varchar(max))
Create table #temp4 (name varchar(max))


insert into #temp3 Select 'Paul'
insert into #temp4 Select 'PAUL'

Select * from #temp3
EXCEPT
Select * from #temp4

Open in new window

qinformationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The collation on each name column must be set to case sensitive, which since it wasn't explicitly declared in the CREATE TABLE #tmp statements I'm guessing it's taking the collation from the database default.  

To view, in SSMS go to the database and do a right-click:Properties, and eyeball the General tab, Collation property.  Looking at the letters in the second group from the right, _CI_ means case insensitive (i.e. Paul will = PAUL), _CS_ means case sensitive (Paul <> PAUL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qinformationAuthor Commented:
Excellent, that was it!  Thanks!
0
qinformationAuthor Commented:
Been doing this for years and years but never had to deal with collation until now.  Always learning!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Wait until you have to study for Microsoft exams, you learn all sorts of goofy cr*p you never had to deal with before.

Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.