sqlsql-servercollate

Case sensitive search query with IN clause - SQL Server

I want to do a case sensitive search in SQL Server environment. Whenever I am using separate queries like below, it is working fine:

select * from table1 where flag = 'Yes' COLLATE sql_latin1_general_cp1_cs_as;
select * from table1 where flag = 'No' COLLATE sql_latin1_general_cp1_cs_as;

But how to achieve this functionality when I am using a IN clause like below:

select * from table1 where flag in ('Yes', 'No'); //This is returning all the flag vlaues like Yes, YES, NO, No
Solution

  • Just like you did your 2 examples, use COLLATE:

    SELECT *
FROM table1
WHERE flag COLLATE sql_latin1_general_cp1_cs_as IN ('Yes', 'No');