Case Sensitive SQL Query Search with Collate - SQL SERVER

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters.That is, "Shindu" is the same as "SHINDU" or "shindu".

If you are using SQL Server, check out the COLLATE clause to make searches case sensitive

The COLLATE clause allows us to specify a particular collation for an expression. In the following examples, we will use COLLATE to make our search case sensitive. We need to specify a case sensitive collation along with COLLATE. The following example uses the collation SQL_Latin1_General_CP1_CS_AS. If you are working with non-English data, choose an appropriate collation.

Example :

Table : Tbl_Login

 The above example print 'Invalid Login Name or Password!', because the input provided is in all lower case, while the data in the table is stored in mixed case.

Changing the collation of the column permanently

Changing the collation of the column permanently, so that all comparisons are case sensitive by default.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE <Table_Name> ALTER COLUMN <Column_Name> VARCHAR(50)
COLLATE Latin1_General_CS_AS