"SELECT DISTINCT" ignores different cases

Question

Hello, I have the problem, that MSSQL Server 2000 should select some distinct values from a table (the specific column is of the nvarchar type). There are the sometimes the same values, but with different cases, for example (pseudocode):

SELECT DISTINCT * FROM ("A", "a", "b", "B")

would return

A,b

But I do want (and do expect)

A,a,b,B

because they actually are different values.

Any idea how to solve this problem?

Thanks a lot in advance!

Answer 1

The collation will be set to case insensitive.

You need to do something like this

Select distinct col1 COLLATE sql_latin1_general_cp1_cs_as
From dbo.myTable

Answer 2

SELECT DISTINCT
   CasedTheColumn 
FROM
   (
   SELECT TheColumn COLLATE LATIN1_GENERAL_BIN AS CasedTheColumn
   FROM myTAble
   )FOO
WHERE
   CasedTheColumn IN ('A', 'a'...)

Answer 3

maby this will help: http://msdn.microsoft.com/en-us/library/ms187582.aspx

Answer 4

Try setting the collation of the column in question to something binary, e.g. utf8-bin. You can either do that in the SELECT statement itself or by changing your table structure directly (which means it doesn't have to map the collation each time the query is run, since it will store it correctly internally).