"SELECT DISTINCT" ignores different cases

Question

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.

How to solve this problem?

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

Not sure about MS SQL but with MySQL or postgres, use BINARY for this operation. Cast the column to binary like so:

SELECT DISTINCT BINARY(column1) from table1; 

Just change column1 and table1 as per your schema.

Full example that works for me in MySQL 5.7, should work for others:

SELECT DISTINCT BINARY(gateway) from transactions; 

Cheers!