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

Case Sensitive search with SQL

By , 27 Dec 2011
Rate this:
Please Sign up or sign in to vote.

Background

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
3. 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,

select.GIF

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

collate.GIF

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

ALTER TABLE Table1 ALTER COLUMN Column1 VARCHAR(20) COLLATE Latin1_General_CS_AS

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

EXEC sp_help tableName

here is the result

StoredProcedure.GIF

Thanks

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

Suggestion are most welcome.

License

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

About the Author

koolprasad2003
Software Developer
India India
MCP,MCTS.
Microsoft MVP 2014 [ASP.NET/IIS]
 
No one play your role BEST.... than U

Comments and Discussions

 
GeneralMy vote of 5 PinmemberElham M21-Aug-12 20:33 
GeneralMy vote of 3 PinmemberKP Lee2-Jan-12 21:44 
GeneralMy vote of 3 Pinmembertecgoblin2-Jan-12 20:29 
GeneralMy vote of 4 PinmemberSuvendu Shekhar Giri28-Dec-11 9:37 
QuestionPost it as a tip PinmemberShameel28-Dec-11 4:08 
AnswerRe: Post it as a tip Pinmemberkoolprasad200329-Dec-11 20:53 
Thanks for suggestion Shameel,
i will enhance it's scope in later versions. Smile | :)
Rating always..... WELCOME
 
The only reason people get lost in thought is because it's unfamiliar territory.

GeneralMy vote of 3 Pinmembersagnik mukherjee27-Dec-11 18:47 
GeneralMy vote of 5 PinmemberPranit Kothari27-Dec-11 2:53 

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 | Mobile
Web04 | 2.8.140415.2 | Last Updated 28 Dec 2011
Article Copyright 2011 by koolprasad2003
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid