20

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gold
  • 60,526
  • 100
  • 215
  • 315

5 Answers5

34

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'
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
6

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?

Irshad
  • 3,071
  • 5
  • 30
  • 51
gbn
  • 422,506
  • 82
  • 585
  • 676
3

Try this just add binary keyword after where:

select * from MyTbl where binary A = 'ABCdef';
Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
Arun R. Prajapati
  • 2,654
  • 1
  • 14
  • 14
1

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

Alex
  • 14,338
  • 5
  • 41
  • 59
1

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).

Josh Anderson
  • 5,975
  • 2
  • 35
  • 48
  • 2
    -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 ;) – TomTom Aug 02 '10 at 11:55
  • 3
    @TomTom - Using `COLLATE` means the indices won't be used anyway. – Martin Smith Aug 02 '10 at 12:05
  • 2
    I'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. – Josh Anderson Aug 02 '10 at 13:19
  • wouldn't an nvarchar be 40 bytes if it was 20 characters long? – ps2goat Sep 24 '13 at 04:09