Case Sensitive String Comparison

Question

I need to compare two strings in a If-Else block in a stored procedure such as

If(@USERNAME='rajat')
begin
select * from table
end
else
begin
select * from table1
end

but the problem with the above approach is '=' comparison is case insensitive.I need a approach where the comparison is case sensitive

possible duplicate of [SQL Case Sensitive String Compare](http://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare) — NickyvV, Nov 28 '13 at 12:45
Possible duplicate. http://stackoverflow.com/questions/3387378/sql-server-queries-case-sensitivity?rq=1 — neildt, Nov 28 '13 at 12:46
case sensitivity has nothing to do with the equals sign. Case sensitivity is determined by the collation for the database — vhadalgi, Nov 28 '13 at 12:46

HTTP 410 · Accepted Answer · 2013-11-28T18:07:59.033

11

SQL Server has case sensitivity at the server, database, and column level. This is part of the collation properties. So in your example, it's likely that one or more of these settings has been set to case-insensitive.

-- Check server collation
SELECT SERVERPROPERTY('COLLATION')

-- Check database collation.
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

-- Check column collation
select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name

Something like this SQL might work for you:

If (@USERNAME='rajat' COLLATE Latin1_General_CS_AS)
.....

edited Nov 28 '13 at 18:07

answered Nov 28 '13 at 12:47

HTTP 410

17,300
12
76
127

+1 For actually providing an explanation with your answer, and other relevant information, rather than just posting working code with no other information. This appears to be a dying art on Stackoverflow. – GarethD Nov 28 '13 at 12:58
Is it more efficient to use a collate casting instead of using upper() or lower function? and collate could depending of the collation used the problem to change some character – Mathese F Nov 28 '13 at 14:01
@MatheseF, upper/lower functions deal only with case sensitivity. Collate also deals with sorting rules and accent sensitivity. I don't know which is more performant. – HTTP 410 Nov 28 '13 at 18:13
@MatheseF, also see answers to this SO question http://stackoverflow.com/questions/6145687/affecting-performance-with-sql-collation. – HTTP 410 Nov 28 '13 at 18:21

score 3 · Answer 2 · answered Nov 28 '13 at 12:47

3

If(@USERNAME='rajat' COLLATE Latin1_General_CS_AS )
begin
select * from table
end
else
begin
select * from table1
end

answered Nov 28 '13 at 12:47

Mikhail Timofeev

2,169
15
13

dav1dsm1th · Answer 3 · 2021-02-03T16:04:30.450

3

Another way to force case sensitivity is to convert/cast both arguments to varbinary types:-

declare @string1 as varchar(50) = 'abc'
declare @string2 as varchar(50) = 'ABC'

if @string1 = @string2
    print 'same'
else
    print 'different'

if convert(varbinary(50),@string1) = convert(varbinary(50),@string2)
    print 'same'
else
    print 'different'

produces:-

same
different

edited Feb 03 '21 at 16:04

answered Nov 29 '13 at 00:26

dav1dsm1th

1,687
2
20
24

Case Sensitive String Comparison

3 Answers3