top button
Flag Notify
Site Registration

Sql server: Check string comparison is case sensitive or not?

+4 votes
Sql server: Check string comparison is case sensitive or not?
posted Jun 8, 2015 by Mohammed Hussain

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
Best answer

In simple word we can say sql server string comparison is case sensitive

If we will compare the strings in sql sever in where clause either using equal to (=) operator or like operator case sensitiveness will depend upon default collation of your database. That is if your collation is case sensitive then comparison will case sensitive and if collation is case insensitive then comparator will not case sensitive.

In sql sever default case default collation is SQL_Latin1_General_CP1_CI_AS. It case insensitive (CI) and ascent sensitive (AS)

DECLARE @Str AS VARCHAR(20) = 'ExactHelp'

IF @Str = 'exacthelp'
    SELECT 'Case insensative'
    SELECT 'Case sensative'

Output: Case insensitive

Sql query to know default collation name of your database name:

SELECT DATABASEPROPERTYEX('Database_Name', 'Collation')

Example of case sensitive comparison in sql sever:

DECLARE @Str AS VARCHAR(20) = 'ExactHelp'

IF @Str = 'exacthelp' COLLATE SQL_Latin1_General_CP1_CS_AS
    SELECT 'Case insensitive'
    SELECT 'Case sensitive'

Output: Case sensitive


SQL_Latin1_General_CP1_CS_AS is case sensitive (CS) as well as ascent sensitive (AS) collation.

answer Jun 11, 2015 by Shivaranjini