SQL Server Case Sensitive Search: Fetching lowercase or uppercase string

Today I came across a requirement to fetch and list down the records from the table having all lower case string data. This is an interesting scenario. The SQL Server database by default is set for case-insensitive. But, I need to do case-sensitive search using select query. Case sensitive search in SQL Server can be achieved either by using COLLATE or by using BINARY_CHECKSUM().

• COLLATE is the T-SQL clause used to define collation.
• BINARY_CHECKSUM() is a built-in system function used to compare the binary check-sum value.

In this article I will show you how to use both the options. In addition to lower case search, I’ll show you the upper case search and mixed case search as well.

If you want to compare the column with a known value of case-sensitive string, then see my other article “Case Sensitive String Comparison on SQL Server“.

To start with the experiment, I have created a table using the below query.

CREATE TABLE [dbo].[MyTecBits_Table_1](

[Sl_no] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Description] [varchar](500) NULL

) ON [PRIMARY]

GO

I’ve inserted records some with all lower case, some with all upper case and some with mixed case.

insert into MyTecBits_Table_1 values ('MYTECBITS','THISWEBSITEHASBITSANDPIECESOFTECHNICALINFORMATION')

insert into MyTecBits_Table_1 values ('mytecbits','thiswebsitehasbitsandpiecesoftechnicalinformation')

insert into MyTecBits_Table_1 values ('MyTecBits','ThisWebSiteHasBitsAndPiecesOfTechnicalInformation')

insert into MyTecBits_Table_1 values ('MY TEC BITS','THIS WEB SITE HAS BITS AND PIECES OF TECHNICAL INFORMATION')

insert into MyTecBits_Table_1 values ('my tec bits','this web site has bits and pieces of technical information')

insert into MyTecBits_Table_1 values ('My Tec Bits','This Web Site Has Bits And Pieces Of Technical Information')

Now out test table with test strings with different case types are ready for our experiment.

Finding all lower case records:

Fetching only the rows having all lowercase in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Lower(Name)

Fetching only the rows having all lowercase in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Lower(Name))

See the results for lower case search:

Finding all upper case records:

Similar to fetching the lower case string, we can search for upper case string just by changing the LOWER string function to UPPER string function. See the example below.

Fetching only the rows having all uppercase in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Upper(Name)

Fetching only the rows having all uppercase in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Upper(Name))

 See the results for upper case search:

Finding only the mixed case records:

Just like upper case and lower case sensitive search, we can search for mixed case records by combining upper and lower case search conditions.

Fetching only the rows having mixed case in one of the column using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)

Fetching only the rows having all mixed case in one of the column using BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Upper(Name)) and BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Lower(Name))

See the results for mixed case search:

Read my other article “Case Sensitive String Comparison on SQL Server” for details on comparing the column with a known value of case-sensitive string or character.