Click here to Skip to main content
11,479,170 members (54,425 online)
Click here to Skip to main content

Tagged as

Case Sensitive search with SQL

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

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)

Share

About the Author

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

Comments and Discussions

 
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 
It's cool.that's one that I want
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
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    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.150520.1 | Last Updated 28 Dec 2011
Article Copyright 2011 by koolprasad2003
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid