Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to change the database server for case sensitive Expand / Collapse
Author
Message
Posted Thursday, March 26, 2009 1:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 12:19 AM
Points: 108, Visits: 248
hi,

In my database I created one table and add one column.

column name is : column1 ..

when i used select query select .

select COLUMN1 FROM table ..

it showing data.. how can i change the database to case sensitive..
I am using sql server 2005 ctp expressedition..

regards:
giri...


Thanks
Dastagiri.D
Post #683866
Posted Thursday, March 26, 2009 2:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,606, Visits: 1,039
So whats the proble it must not show column1 when give COLUMN1.

I dont think there is an option for that.


Tanx
Post #683904
Posted Friday, March 27, 2009 5:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 2,488, Visits: 1,135
Hi,

you can change the server collation, check http://msdn.microsoft.com/en-us/library/ms179254.aspx

Regards,
René
Post #684894
Posted Friday, March 27, 2009 5:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
Points: 371, Visits: 437
Note that you can also change your database, or table collation as well (if you don't want your entire server running that way)

So if you're using SQL_Latin1_General_CP1_CI_AS, try SQL_Latin1_General_CP1_CS_AS

ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CS_AS
Post #684907
Posted Friday, March 27, 2009 6:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
Why do you want to change to case sensitive?

"Keep Trying"
Post #684965
Posted Friday, March 27, 2009 9:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
You do not have to change the database or column collation in order to use different collations for specific tasks. In fact you can mix collations to match your needs.

If you have your database built with case insensitve collation but would like to do case sensitive search just use the COLLATE keyword.

For example the first select will find all records where test code is 'ABC' regardless of the case - so abc, AbC aBc and such will be found. The second one will find only the record matching the case of the specified string 'ABC' in this case. The third example will mix the case sensitive and case insensitive search in one query.

SELECT * FROM Tests
WHERE TestCode = 'ABC'

SELECT * FROM Tests
WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS

SELECT * FROM Tests
WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS
OR TestCode = 'CBS'



---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #685167
Posted Friday, March 27, 2009 1:13 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 908, Visits: 2,803
Note that changing server or database collation does NOT change the collation for pre-existing columns/data. There's only one way to do that:
Create a brand new empty database in the desired collation and migrate the data into that DB.

Simplest thing to do is to use the COLLATE option, though (as mentioned above).

MSSQLTIPS just had an article on that today: slightly different context but same issue
Post #685340
Posted Friday, March 27, 2009 6:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:07 PM
Points: 1,688, Visits: 8,760
Read about it at http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

MJ
Post #685478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse