How to make sql server data as case sensitive?

You put COLLATE SQL_Latin1_General_CP1_CS_AS next to column name...

In the above CS refers Case Sensitive
You can get the Collation name

SELECT SERVERPROPERTY ('Collation'); -- server collation
SELECT name, collation FROM sys.syscolumns ;-- column name and Collation

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

sample example,

I have table named as EMPLOYEES with column name LAST_NAME='Abel'

-- retruns 1 record
SELECT LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS FROM EMPLOYEES
WHERE LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS = 'Abel'

-- retruns Zero record. Because it searches for case sensitive data
SELECT LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS FROM EMPLOYEES
WHERE LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS = 'abel'


EDIT:

If the above two replies help you, click on "Mark as Answer " link

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

if you wish to change collation of the column in the table structure itself you can make use of " ALTER TABLE tablename ALTER COLUMN" statement.....
if u want i will provide you the complete syntax.....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi

Why do you have used COLLATE @ SELECT clause Too ?

If you want to force statement level of Case Sensitive then its enough the following one.... right ?

SELECT LAST_NAME FROM EMPLOYEES
WHERE LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS = 'Abel'

Do you want to say anything else ?

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

i know jasmin.. if user wants to store it into another table and column collation as case sensitive that time my query is useful.. that is the reason i posted like that

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

If you want to add case sensitive to your database. Use below query

Syntax:
ALTER DATABASE DB_NAME COLLATE LATIN1_GENERAL_CS_AS

Ex:
ALTER DATABASE MYDB COLLATE LATIN1_GENERAL_CS_AS

If you want to change to case insensitive just use CI in place of CS

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

@Bandi

User(s) may be beginners. So, How do they know that "What you were thinking?". (IF/IF NOT kind of thing)

So, If you want to say something more. You have to sepcify that too!

Do you think, You can store the result of the following into another Table with CASE SENSITIVE???

-- retruns 1 record
SELECT LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS FROM EMPLOYEES
WHERE LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS = 'Abel'

-- retruns Zero record. Because it searches for case sensitive data
SELECT LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS FROM EMPLOYEES
WHERE LAST_NAME COLLATE SQL_Latin1_General_CP1_CS_AS = 'abel'

1. If you want to use "SELECT...INTO" it won't work!!!
2. If you want to use "INSERT TableName SELECT..." That table should be created earlier before using it!!!

If one of the user want to store into an another NEW table. Can they use above query ?? Will it work ?

Do you aggree ?

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

hello jasmine,
OP's wrote this >> retrive particular field data from sql server and that should be case sensitive.

by looking at this mostly we will provide retrieval statement only....
once he face any diffixulty i will assure that provide him better solution with proper explanation...

this is not for arguement jasmine.....
we are helping them, but not creating doubts in their mind...
if you wish to provide solution help him.....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

Ok Bandi

I just wanted to clear/clarify the thing. Thats it!

Please don't take it personally!

Forum Admin will take care of it!

Dear Forum Admin, If you feel my concern was wrong Or Misleading, Kindly BLOCK me from this forum (Or) If my concern is correct. Kindly give your feedback!!!

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator

select [Name] from Employee where [Email] = 'brahmam.naidu98@gmail.com' and [Address] = 'Brahmam' COLLATE SQL_Latin1_General_CP1_CS_AS

brahmam

Hanishani, if this helps please login to Mark As Answer. | Alert Moderator