In this tutorial, we are going to learn Case Sensitive Search in SQL Server. Many applications need the functional requirement that they need to make a Case Sensitive Search for example UserName and password.

Introduction:

The default collation for SQL Server is not case sensitive however it is possible to collation of any column of a table to make it Case Sensitive.

To check collation of SQL Server, run this query:

SELECT SERVERPROPERTY('COLLATION')

Output:

SQL_Latin1_General_CP1_CI_AS

Where;

CI specified Case Insensitive.

Case Sensitive Search in SQL Server:

Create Table:

Create TABLE [dbo].[CaseSensitiveSearch]
(
CaseSensitiveColumn VARCHAR(100)
)

Insert Records:

INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('Debugonweb')
INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('debugOnweb')
INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('DebugonWeb')
INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('DebugOnWeb')
INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('debugonweb')
INSERT INTO [dbo].[CaseSensitiveSearch] ([CaseSensitiveColumn])
VALUES
('DEBUGONWEB')

You can see I have inserted records in different cases. I will run a simple query with a simple condition on CaseSensitiveColumn.

SELECT * FROM [dbo].[CaseSensitiveSearch] WHERE [CaseSensitiveColumn]='DebugOnWeb'

Case Sensitive Search

This query will return all 6 rows rather than the matching value based on the case. If I want to Search records based on Case then we need to change the query as follows:

SELECT * FROM [dbo].[CaseSensitiveSearch] WHERE [CaseSensitiveColumn] COLLATE Latin1_General_CS_AS='DebugOnWeb'

Case Sensitive Search

To Add a collation to any column use the following query:

Create TABLE [dbo].[CaseSensitiveSearch]
(
CaseSensitiveColumn VARCHAR(100) COLLATE
Latin1_General_CS_AS

)

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

ALTER TABLE  [dbo].[CaseSensitiveSearch]
ALTER COLUMN CaseSensitiveColumn  VARCHAR(100)
COLLATE Latin1_General_CS_AS

View More:

Conclusion:

I hope you understand how to make Case Sensitive Search in SQL Server. I would like to have your feedback, comments, and suggestions.

Thank You.

Leave a Comment