Case Sensitive search with SQL
This article gives you idea about how to do Case Sensitive search with MSSQL. Many times we need to do Case Sensitive search in database. This article will drive you through all Queries need to do Case Sensitive search. Find Case Sensitive search with SQL.
Learn Case Sensitive search with SQL
last week our senior told me to select records from database starts with 'r' (small R). it troubles a lot, and we got solution finally.
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 ?
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).
default collation is Latin.
Get into action
suppose I have a table named 'users' contains 4 records.
if we fire Query "select * from users where name = 'responsive''
it will return me all columns cause all column contains same data and search is not case Sensitive.
To fetch case sensitive records you need to change collation of that column.
Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.
here is syntax
SELECT Column1 FROM Table1 WHERE Column1 = 'expression'
here we go
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)
To know the collation of the column for any table run following Stored Procedure
EXEC sp_help tableName
here is the result
This simple article gives you a short idea of casesensitive search in database.
Suggestion are most welcome
We can also use VarBinary conversion to search using case-sensitive