CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
needfulthing
needfulthing

Reputation: 1096

How to compare an IN list case sensitive

The statement

SELECT * FROM table WHERE some_field IN ('abc', 'def', 'ghi')

ignores case, so records are also found when some_field is 'ABC' for example.

Placing a COLLATE after the list unfortunately produces a syntax error. Does anyone know how to compare an IN list case sensitive without changing the (insensitive) collation setting for the DB or single field?

Upvotes: 1

Views: 84

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You need to use COLLATE after column name:

SELECT * FROM table 
WHERE some_field COLLATE <collation_name>_CS IN ('abc', 'def', 'ghi');

db<>fiddle demo

Upvotes: 2

Related Questions