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')

SQL-Server-Case-Sensitive-Search-1

 

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:

SQL-Server-Case-Sensitive-Search-2

 

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:

SQL-Server-Case-Sensitive-Search-3

 

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:

SQL-Server-Case-Sensitive-Search-4

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.

 

8 comments for “SQL Server Case Sensitive Search: Fetching lowercase or uppercase string

  1. Mark Jones
    March 31, 2016 at 2:48 pm

    Thanks, worked very well

  2. Janet Mollison
    July 10, 2015 at 10:31 pm

    Worked like a dream! You have saved me a great deal of additional work trying to find out how to do this – I’m immensely grateful – thank you.

  3. Guest
    January 22, 2015 at 9:12 am

    Thanks a bunch. Worked perfectly for what I needed.

  4. Valerian
    September 23, 2014 at 4:27 am

    We’ve used the COLLATE method before but using BINARY_CHECKSUM is so much neater and cleaner.

  5. kimsteinmetz
    June 13, 2014 at 2:19 pm

    thanks, I was trying some other things that I found on google and none of them worked for me, but your suggestion did. I appreciate it.

  6. Rajib
    April 9, 2014 at 11:11 am

    Very good example and usefull

  7. Rich Binder
    December 11, 2013 at 10:32 am

    Thanks very much for this article. It really helped me out in searching through a table for certain records to be updated.

  8. Mayank Vishnoi
    September 23, 2013 at 6:45 am

    Awesome Tutorial… really Helpful.. Thanks a lot.

Leave your thoughts...