Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Should we make SQL queries case sensitive?

December 8 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Should we make SQL queries case sensitive? Yes, we should.

We have so many ways to get the data from the SqlServer with Case Sensitive.

Ex :
Create table mytab
(id int,
 Name varchar(50))


Insert into mytab (1,'aaaa')
Insert into mytab (1,'AAaa')
Insert into mytab (1,'aAaa')
Insert into mytab (1,'aaAA')
Insert into mytab (1,'aaaA')


If you installed SQL Server with the default collation options you will get the same result for the below queries


Select * from mytab where Name = 'aaaa'    Output : 5 Rows
Select * from mytab where Name = 'AAaa'    Output : 5 Rows
Select * from mytab where Name = 'aAaa'    Output : 5 Rows


Alter above queries by forcing collation at the column level to get the Case sensitive results

Select * from myTab where Name COLLATE Latin1_General_CS_AS = 'aaaa'

Return only one row by Case Sensitive


If you want set the collation to entire Table you need to alter the above table like.


i) Need to find the Collate Identifier for that column
    Ex : Exec sp_help 'myTab'
   
The Output will be

    Column Name   Type           Collation
    Id            Int               NULL
    Name         Varchar    SQL_Latin1_General_CP1_CI_AS


Alter your table with Collate identifier

ALTER TABLE myTab
    ALTER COLUMN Name VARCHAR(50)
    COLLATE SQL_Latin1_General_CP1_CI_AS   

After Alter you table, you will get the Case sensitive result for the below queries also
   

Select * from mytab where Name = 'aaaa'    Output : 1 Row
Select * from mytab where Name = 'AAaa'    Output : 1 Row
Select * from mytab where Name = 'aAaa'    Output : 1 Row
   







Share this post

Repost 0

Comment on this post

Custom Term Papers 06/04/2010 14:38



It is good to share information which may help students in their academic career. good job.


 


Custom Term Papers



term paper help 01/16/2010 12:19


I have been searching information by visiting several blogs and articles online. But this page has turned out to be the ultimate destination of my desired knowledge. This has no doubt really
fulfilled my contention and for that I am thankful to the blogs owner! Regards.

college term paper | custom term paper