How to compare 2 strings in SQL server which should not be true if any case difference

Question

Trying to compare the 2 strings which should return true if both strings are matching in case otherwise should be return false

In the below example, both strings are not matching due to case.

Is there any way to implement this in SQL Server 2016 Example:

Declare @str1 varchar (100) ='Yes'
Declare @str2 varchar (100) ='YES'
    Select 
         Case When @str1=@str2 then 'same'
         Else 'Not same'
    END

Expected Result: Not same

Use a case sensitive collation – Caius Jard Jul 02 '20 at 06:25 — Caius Jard, Jul 02 '20 at 06:25

score 5 · Accepted Answer · answered Jul 02 '20 at 06:32

5

You can do this by using a case sensitive collation, e.g. Latin1_General_CS_AS. You can set a default collation for your database, but you can also apply the collation in a specific query. In your case:

Declare @str1 varchar (100) ='Yes'
Declare @str2 varchar (100) ='YES'
    Select 
         Case When @str1 COLLATE Latin1_General_CS_AS = @str2 COLLATE Latin1_General_CS_AS then 'same'
         Else 'Not same'
    END

answered Jul 02 '20 at 06:32

Wouter

2,881
2
9
22

Indeed it is (alas) not possible to declare the collation of a variable so the collation has to be mentioned every time the variable is used :/ – Caius Jard Jul 02 '20 at 06:40

score 1 · Answer 2 · answered Jul 02 '20 at 06:40

Another alternative is

Declare @str1 varchar (100) ='Yes'
Declare @str2 varchar (100) ='YES'

    Select 
         Case When HASHBYTES( 'sha1', @str1)=HASHBYTES('sha1', @str2) then 'same'
         Else 'Not same'
    END

SQL Case Sensitive String Compare

How to compare 2 strings in SQL server which should not be true if any case difference

2 Answers2