SQL Server Distinct – Make it Case Sensitive

By default SQL Server DISTINCT is NOT case sensitive. This means that if you had the following data in a table:

FirstNames
Richard
Adam
James
Adam
Ryan
RICHARD
Richard

and queried this table with “SELECT DISTINCT FirstNames FROM table” you would get:

FirstNames
Richard
Adam
James
Ryan

However, there are occasions where you want the case taken into account. Change the query to include a COLLATE function with case sensitive (notice the cs) and it then does this.

SELECT DISTINCT FirstNames COLLATE sql_latin1_general_cp1_cs_as As [FirstNames] FROM table

now returns:

FirstNames
Richard
Adam
James
Ryan
RICHARD

 

Its really handy to know and remember, especially if you are getting errors when trying to select a value from a list and the distinct name isn’t there because the exact same text is in the field with a slightly different case.

Leave a Reply