SQL Server queries case sensitivity
Solution 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'
Solution 2
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
?
Solution 3
Try this just add binary keyword after where
:
select * from MyTbl where binary A = 'ABCdef';
Solution 4
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 field is sized at 20. For nvarchar double it (thanks @ps2goat).
Solution 5
It's all about collation. Each one has a suffix (CI and CS, meaning Case Insensitive, and Case Sensitive).
Gold
Updated on July 09, 2022Comments
-
Gold almost 2 years
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
-
TomTom almost 14 years-1 Whow. That is the worst answer possible - take out all indices, force a table scan and totally ignore the reality of being able to change the collation ;)
-
Martin Smith almost 14 years@TomTom - Using
COLLATE
means the indices won't be used anyway. -
Josh Anderson almost 14 yearsI'm sure there are worse answers -- that query at least functions. From my understanding there's no way to specify case insensitivity using indexes unless you're doing a binary comparison. I've never tried it, but if your table column was defined as a VARBINARY, you could do an index on that and only have to cast your search string.
-
ps2goat almost 11 yearswouldn't an nvarchar be 40 bytes if it was 20 characters long?