Click here to Skip to main content
11,924,280 members (62,821 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


12 bookmarked

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

You may also be interested in...

Comments and Discussions

Questionwhat should I use for ARABIC COLLATION DB ? Pin
vsva chintu13-Nov-15 19:55
professionalvsva chintu13-Nov-15 19:55 
AnswerRe: what should I use for ARABIC COLLATION DB ? Pin
koolprasad200315-Nov-15 18:46
memberkoolprasad200315-Nov-15 18:46 
Generalcomment. Pin
Mohammad Ahmer9-Feb-15 20:12
memberMohammad Ahmer9-Feb-15 20:12 
QuestionMS Access Pin
rojaldearintok29-Oct-14 17:11
memberrojaldearintok29-Oct-14 17:11 
GeneralMy vote of 5 Pin
Elham M21-Aug-12 21:33
memberElham M21-Aug-12 21:33 
GeneralMy vote of 3 Pin
KP Lee2-Jan-12 22:44
memberKP Lee2-Jan-12 22:44 
GeneralMy vote of 3 Pin
tecgoblin2-Jan-12 21:29
membertecgoblin2-Jan-12 21:29 
You should indeed post this as a tip
GeneralMy vote of 4 Pin
Suvendu Shekhar Giri28-Dec-11 10:37
memberSuvendu Shekhar Giri28-Dec-11 10:37 
QuestionPost it as a tip Pin
Shameel28-Dec-11 5:08
memberShameel28-Dec-11 5:08 
AnswerRe: Post it as a tip Pin
koolprasad200329-Dec-11 21:53
memberkoolprasad200329-Dec-11 21:53 
GeneralMy vote of 3 Pin
sagnik mukherjee27-Dec-11 19:47
membersagnik mukherjee27-Dec-11 19:47 
GeneralMy vote of 5 Pin
Pranit Kothari27-Dec-11 3:53
memberPranit Kothari27-Dec-11 3:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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
Web03 | 2.8.151125.3 | Last Updated 28 Dec 2011
Article Copyright 2011 by koolprasad2003
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid