RMTWeb

View Original

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:FirstNamesRichardAdamJamesAdamRyanRICHARDRichardand queried this table with "SELECT DISTINCT FirstNames FROM table" you would get:FirstNamesRichardAdamJamesRyanHowever, 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:FirstNamesRichardAdamJamesRyanRICHARD 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.