SQL Server queries case sensitivity

Question

hi

I have this database:

abcDEF

ABCdef

abcdef

if I write: select * from MyTbl where A='ABCdef'

how to get: ABCdef

and how to get:

abcDEF

    ABCdef

    abcdef

Thanks in advance

forgot to write - sqlCE

Answer 1

You can make your query case sensitive by making use of the COLLATE keyword.

SELECT A 
FROM MyTbl 
WHERE A COLLATE Latin1_General_CS_AS = 'ABCdef'

Answer 2

It's all about collation. Each one has a suffix (CI and CS, meaning Case Insensitive, and Case Sensitive).

http://www.databasejournal.com/features/mssql/article.php/10894_3302341_2/SQL-Server-and-Collation.htm

Answer 3

SQL is non-case-sensitive by default, so you will get all three items if doing a simple string comparison. To make it case-sensitive, you can cast the value of the field and your search value as varbinary:

SELECT * FROM MyTbl WHERE CAST(A AS varbinary(20)) = CAST('ABCdef' as varbinary(20))

The above assumes your varchar or nvarchar field is sized at 20.

Answer 4

If you have abcDEF, ABCdef, abcdef already in the database then it's already case sensitive or you have no constraint.

You'd have to add a COLLATE on both sides to make sure it's truly case sensitive (for a non case sensitive database) which will invalidate index usage

SELECT TheColumn
FROM MyTable 
WHERE TheColumn COLLATE Latin1_General_CS_AS = 'ABCdef' COLLATE Latin1_General_CS_AS

What about accents too? Latin1_General_CS_AI, Latin1_General_Bin?

Answer 5

For SQL Compact, you can make the entire database case senstive - http://erikej.blogspot.com/2008/07/working-with-case-sensitive-sql-compact.html -

To get all 3 rows do

select * from MyTbl where LOWER(A)='abcdef'