-1

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
Leo
  • 5,017
  • 6
  • 32
  • 55

2 Answers2

4

Just like you did your 2 examples, use COLLATE:

SELECT *
FROM table1
WHERE flag COLLATE sql_latin1_general_cp1_cs_as IN ('Yes', 'No');
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 2
    The COLLATE clause can prevent indexes from being used so one can add `AND flag IN ('Yes', 'No')` so that the insensitive search may be done via an index seek and the case-sensitive predicate used to further filter the rows. – Dan Guzman May 23 '19 at 09:44
1

Set the column collation properly, and you're done - for ever!

ALTER TABLE table1 ALTER COLUMN flag varchar(3) COLLATE sql_latin1_general_cp1_cs_as

Now you can simply do:

select * from table1 where flag = 'Yes';
select * from table1 where flag in ('Yes', 'No'); 
jarlh
  • 42,561
  • 8
  • 45
  • 63