Searching Lowercase data in MS SQL Server

A few days back I came across a requirement to fetch the records from the table having all lowercase letters. This was an interesting scenario. The SQL Server database by default is set for case-insensitive. But, I need to do a case-sensitive search. Case-sensitive search in SQL Server can be achieved in two ways. 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 checksum value.

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

To start with the experiment, let’s create a table.

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

Insert records some with all lowercase, some with all uppercase 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 our test table with test strings with different case types is ready for our experiment.

Fetching all lowercase records:

Fetching only the rows having all lowercase in one of the columns 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 columns using the BINARY_CHECKSUM() method.

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

See the results for lower case search:

 

Fetching all uppercase records:

Similar to fetching the lowercase string, we can search for an uppercase string just by changing the LOWER string function to the UPPER string function. See the example below.

Fetching only the rows having all uppercase in one of the columns 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 columns using the BINARY_CHECKSUM() method.

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

See the results for upper case search:

Fetching only the mixed case records:

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

Fetching only the rows having mixed cases in one of the columns 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 cases in one of the columns using the 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:

Click here to read more on the Databases.

[This article was originally published here]

Exit mobile version