To make SQL Queries Case Sensitive

Guru
Posted by in Sql Server category on for Beginner level | Views : 11160 red flag

To make SQL Queries Case Sensitive using Collation.
You can make SQL Queries Case sensitive at the column level or Table level or DataBase level


 Download source code for To make SQL Queries Case Sensitive

Introduction

How to make SQL queries case sensitive.

How to do it?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable

(

mycolumn VARCHAR(10)

)

GO

SET NOCOUNT ON

INSERT mytable VALUES('Case')

GO

SELECT mycolumn FROM mytable WHERE

mycolumn='Case'

SELECT mycolumn FROM mytable WHERE

mycolumn='caSE'

SELECT mycolumn FROM

mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT mycolumn FROM mytable WHERE

mycolumn = 'case' AND

CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE

mycolumn = 'case' AND

CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE

mycolumn = 'case' AND

CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10))

-- if myColumn has an index, you will likely benefit by adding

-- AND myColumn = 'case'

 

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query: 

 

SELECT DATABASEPROPERTYEX('<databasename>', 'Collation')

As changing this setting can impact applications and SQL queries, I would isolate this test first. In SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

EXEC sp_help
'mytable'

The second recordset should contain the following information, in a default scenario:

 

Column_Name Collation 
----------- ---------------------------------------------- 
mycolumn    SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

 

ALTER TABLE mytable

ALTER COLUMN mycolumn

VARCHAR(10)

COLLATE

Latin1_General_CS_AS

GO

SELECT mycolumn FROM mytable WHERE

mycolumn='Case'

SELECT mycolumn FROM mytable WHERE

mycolumn='caSE'

SELECT mycolumn FROM

mytable WHERE mycolumn='case'

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously): 

 

ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS

Conclusion
This way to make sql querie Case Sensitive
Page copy protected against web site content infringement by Copyscape

About the Author

Guru
Full Name: sasi kumar
Member Level: Starter
Member Status: Member
Member Since: 10/7/2008 4:12:22 AM
Country:



Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)