SQL Case Sensitive String Compare

Question

How do you compare strings so that the comparison is true only if the cases of each of the strings are equal as well. For example:

Select * from a_table where attribute = 'k'

...will return a row with an attribute of 'K'. I do not want this behaviour.

It might not be what you need but you can change the Collation or use a specific Collation in your query. — Kane, Oct 19 '10 at 13:58

score 498 · Accepted Answer · answered Oct 19 '10 at 13:57

498

Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS

Did the trick.

answered Oct 19 '10 at 13:57

amccormack

13,207
10
38
61

8

I'd normally use Latin1_General_Bin – gbn Oct 19 '10 at 17:53
3

Yes, the Standard approach is to use a case-insensitive collation, though the collations themselves are vendor-specific. Is yours SQL Server syntax? – onedaywhen Oct 20 '10 at 09:24
1

In my case, I have 1 column in my db that is case-sensitive. I needed to compare it to a standard (CI) column. I used a variation of this WHERE Foo.Bar = (Baz.Bar COLLATE Latin1_General_CS_AS) – Hypnovirus Jun 05 '14 at 15:41
3

Thanks but what is Latin1_General_CS_AS ?? Is it special keyword? – Vijay Singh Rana Apr 02 '15 at 04:55
3

@VijaySinghRana `Latin1_General_CS_AS` is a specification of the collation. Collation refers to a set of rules that determine how data is sorted and compared. See [this page](http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm) for more information. – amccormack Apr 03 '15 at 01:50
@amccormack Is this work all types of DB? How about Netezza – Shabar Oct 31 '15 at 09:35
In SQL Server, I would recommend checking the current collation of the table first. For example, if the collation is `Traditional_Spanish_CI_AS`, use `Traditional_Spanish_CS_AS` in your query – Mr Lister Feb 20 '19 at 07:56
will there be performance issues when it comes queried by thousands of users (for example login)? – Leary Jan 13 '20 at 04:57
This also works well inline for JSON columns like this: json_value( tableName.jsonColumnName COLLATE Latin1_General_CS_AS --For Case-sensitive matching of extracted values. , '$.sectionName.attributeName' ) = 'MYUPPERVALUE' – EnocNRoll - AnandaGopal Pardue Jun 07 '23 at 16:01

score 61 · Answer 2 · edited Mar 18 '13 at 07:58

61

You can also convert that attribute as case sensitive using this syntax :

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(200)
COLLATE SQL_Latin1_General_CP1_CS_AS

Now your search will be case sensitive.

If you want to make that column case insensitive again, then use

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(200)
COLLATE SQL_Latin1_General_CP1_CI_AS

edited Mar 18 '13 at 07:58

molnarm

9,856
2
42
60

answered Mar 18 '13 at 07:33

Jugal

611
5
2

I have nvarchar type column. Above query didn't work for me. Any reason? – sandeep.gosavi Sep 21 '21 at 03:21

score 31 · Answer 3 · answered Dec 01 '14 at 22:48

You Can easily Convert columns to VARBINARY(Max Length), The length must be the maximum you expect to avoid defective comparison, It's enough to set length as the column length. Trim column help you to compare the real value except space has a meaning and valued in your table columns, This is a simple sample and as you can see I Trim the columns value and then convert and compare.:

CONVERT(VARBINARY(250),LTRIM(RTRIM(Column1))) = CONVERT(VARBINARY(250),LTRIM(RTRIM(Column2)))

Hope this help.

score 23 · Answer 4 · answered Aug 10 '16 at 08:56

23

Just as another alternative you could use HASHBYTES, something like this:

SELECT * 
FROM a_table 
WHERE HASHBYTES('sha1', attribute) = HASHBYTES('sha1', 'k')

answered Aug 10 '16 at 08:56

Dave Sexton

10,768
3
42
56

3

What about collisions? It'd be rare but I assume there would be multiple strings that hash to the same value. – David Klempfner Nov 23 '18 at 07:43
Yes possible, but extremely rare on such a simple string example I would have thought. – Dave Sexton Nov 23 '18 at 08:49
3

@DavidKlempfner why not first do the compare and if they match then also check for hashbytes? We could make this a function and invoke it like StringsAreCaseSensitiveEqual(a, b) => a = b AND HASHBYTES('sha1', a) = HASHBYTES('sha1', b) – Demetris Leptos Dec 17 '19 at 09:26

score 11 · Answer 5 · answered Feb 26 '21 at 22:18

simplifying the general answer

SQL Case Sensitive String Compare

These examples may be helpful:

Declare @S1 varchar(20) = 'SQL'
Declare @S2 varchar(20) = 'sql'

  
if @S1 = @S2 print 'equal!' else print 'NOT equal!' -- equal (default non-case sensitivity for SQL

if cast(@S1 as binary) = cast(Upper(@S2) as binary) print 'equal!' else print 'NOT equal!' -- equal

if cast(@S1 as binary) = cast(@S2 as binary) print 'equal!' else print 'NOT equal!' -- not equal

if  @S1 COLLATE Latin1_General_CS_AS  = Upper(@S2) COLLATE Latin1_General_CS_AS  print 'equal!' else print 'NOT equal!' -- equal

if  @S1 COLLATE Latin1_General_CS_AS  = @S2 COLLATE Latin1_General_CS_AS  print 'equal!' else print 'NOT equal!' -- not equal

The convert is probably more efficient than something like runtime calculation of hashbytes, and I'd expect the collate may be even faster.

score 3 · Answer 6 · answered Oct 19 '10 at 13:56

3

You can define attribute as BINARY or use INSTR or STRCMP to perform your search.

answered Oct 19 '10 at 13:56

MatTheCat

18,071
6
54
69

This reply doesn't seem to be about SQL Server per the question tag. That DBMS lacks these `INSTR` and `STRCMP` functions. – Jonas Dec 04 '19 at 11:11

SQL Case Sensitive String Compare

6 Answers6

Linked

Related