Click here to Skip to main content
Click here to Skip to main content

Tagged as

Case Sensitive search with SQL

, 27 Dec 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
his article explains you how to deal with Case Sensitive search with SQL


Last week our senior told us to select records from database starts with 'r' (small R). it troubles a lot, but we got solution finally.

Many applications have a functional requirement for the located database to have a case-sensitive search or sort-order, that character data related operations are case-sensitive.

may be there are some situation where you need to find case sensitive records from database.

How to do that ?

To achive this task we need to take help of SQL Collation.

What is SQL Collation ?

MSDN Says:

"Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast."

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet such as Latin1_General (the Latin alphabet used by western European languages).

In simple words we bring together all rules and applied it on table or column defination. default collation is for SQL is Latin.

Get into action

suppose i have a table named 'users' contains 4 records.
1. responsive
2. Responsive
4. ResPONsive

If we fire following Query,
"select * from users where name = 'responsive'"
it will return us all columns. Cause, all column contains same data and record search is not case Sensitive.

following image clear idea,


To fetch case sensitive records you need to change collation of that column.

Change column collation

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS and this is not case sensitive. here is syntax to change column collation.

SELECT Column1 FROM Table1 WHERE Column1 = 'expression'

here we go


Yes. we gor exact result.

by above method, we change column collate for temporary use. but we can change it's collate permanently. by using following Query


To know the collation of the column for any table run following Stored Procedure

EXEC sp_help tableName

here is the result



This simple and short article gives you a suggestion "How to do casesensitive search in database",

Suggestion are most welcome.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
India India
Microsoft MVP 2014 [ASP.NET/IIS]
Click here for more .NET Tips

Comments and Discussions

Generalcomment. PinmemberMohammad Ahmer9-Feb-15 20:12 
QuestionMS Access Pinmemberrojaldearintok29-Oct-14 17:11 
GeneralMy vote of 5 PinmemberElham M21-Aug-12 21:33 
GeneralMy vote of 3 PinmemberKP Lee2-Jan-12 22:44 
GeneralMy vote of 3 Pinmembertecgoblin2-Jan-12 21:29 
GeneralMy vote of 4 PinmemberSuvendu Shekhar Giri28-Dec-11 10:37 
QuestionPost it as a tip PinmemberShameel28-Dec-11 5:08 
AnswerRe: Post it as a tip Pinmemberkoolprasad200329-Dec-11 21:53 
GeneralMy vote of 3 Pinmembersagnik mukherjee27-Dec-11 19:47 
GeneralMy vote of 5 PinmemberPranit Kothari27-Dec-11 3:53 
Thx. Useful!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150301.1 | Last Updated 28 Dec 2011
Article Copyright 2011 by koolprasad2003
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid