Case sensitive matching in IN clause in SQL

Question

I have 2 rows in my table like this

I want a case sensitive comparison.

I wrote a query like this

select *  
from table1 
where name in ('charlie', 'Frank') COLLATE Latin1_General_CS_AS;

but it is not working. What should be the query?

You want case INSENSITIVE or SENSITIVE matching? Your collate is `CS` — Ian Mercer, Mar 22 '20 at 04:02
Possible duplicate https://stackoverflow.com/questions/5039211/what-does-collate-sql-latin1-general-cp1-ci-as-do — Ian Mercer, Mar 22 '20 at 04:02
@TimBiegeleisen, i am new to sql, i dont know what collation is. — anonymous_coder, Mar 22 '20 at 05:25
"but it is not working." That's not telling us anything. Also, SQL is too general. What rdbms are you working with? Please read the first couple of paragraphs in the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. — Zohar Peled, Mar 22 '20 at 07:52
[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) — , Mar 22 '20 at 08:52

score 1 · Accepted Answer · answered Mar 22 '20 at 19:55

You need to change your column to case sensitive. Something like:

ALTER TABLE table1
ALTER COLUMN name VARCHAR(100) 
COLLATE SQL_Latin1_General_CP1_CS_AS

See https://stackoverflow.com/a/485394/224370 which is a slightly different question, but same answer applies.

1 Answers1