Click here to Skip to main content
13,869,141 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


12 bookmarked
Posted 27 Dec 2011
Licenced CPOL

Case Sensitive search with SQL

, 27 Dec 2011
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
Hi there, I am Prasad. Author, Blogger, contributor and passionate about Microsoft .NET technologies. I like to write an articles/blogs on different .NET aspects and like to help Developers, to resolve their issues and boost them on Microsoft Technologies.

Certifications: Microsoft Certified professional (MCP), Microsoft Certified technology specialist (MCTS), Agile-Scrum Master.

Awards: Microsoft Re-connect MVP (GSC Member), Most valuable member at dotnetspider, Most popular curator, Most active curator, featured curator at Microsoft Curah, Editor at dotnetspider.

Microsoft MVP 2014 [ASP.NET/IIS]
Click here for more .NET Tips
-After all Knowledge is an endless entity

You may also be interested in...

Comments and Discussions

Questionwhat should I use for ARABIC COLLATION DB ? Pin
S.V.Aaditya.S.V13-Nov-15 19:55
professionalS.V.Aaditya.S.V13-Nov-15 19:55 
AnswerRe: what should I use for ARABIC COLLATION DB ? Pin
koolprasadd15-Nov-15 18:46
professionalkoolprasadd15-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 
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
professionalShameel28-Dec-11 5:08 
AnswerRe: Post it as a tip Pin
koolprasadd29-Dec-11 21:53
professionalkoolprasadd29-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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190214.1 | Last Updated 28 Dec 2011
Article Copyright 2011 by koolprasadd
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid