CS data in CI-named columns

Question

CS data in CI-named columns

Peter Trevor

Old Hand

Points: 342
More actions
December 26, 2005 at 7:47 am

#364493

I need to convert a database from InterBase 6 to MSDE 2000. The source database makes extensive use of UDTs and character set ISO8859_1 (which is case sensitive). However, if I set the database collation to Latin1_General_CS_AS then the column names and table names are also case sensitive in any SQL statements (which causes issues elsewhere in the project). On the other hand, if I want to set the collation of individual columns instead then there doesn't seem to be a way of setting the collation of a UDT or of a column that is defined by a UDT.
To summarise: I want column and table names to be case insensative, I want character data in those columns to be case sensitive. How do I do that? Any ideas?
Regards PLST

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 1

You can set the column level collation to case sensitive on the appropriate columns without affecting table and column names, i.e.,

alter table <table_name>

alter column <column_name> [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL

You could also leave the columns alone and use "COLLATE Latin1_General_CS_AS" every time you evaluate one of the columns that should be case sensitive, i.e.,

join on <column1> = <column2> COLLATE Latin1_General_CI_AS

or this

where <column> = 'value' COLLATE Latin1_General_CI_AS

Peter Trevor Old Hand Points: 342 More actions · Answer 2

Thanks Ken, but I can't set the collation of a column if that column is a UDT.

Regards PLST

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 3

since sp_addtype just uses the default collation for the UDT's base datatype, after calling the procedure to create your type, you could manually change the collation for the UDT in the systypes table. You'll need to change the collation before using the UDT in a table or that table may not pick up the new collation.

Turn on allow system table edits and try this demo query (I don't suppose it's the "official" way of doing this but, what the hey!)...

USE tempdb

EXEC sp_addtype tryThis, 'VARCHAR(9)', 'NOT NULL'

GO

create table deleteMe2(

pk int not null primary key,

data tryThis)

insert deleteMe2 (pk, data) values (1, 'gotcha')

select * from deleteMe2 where data = 'gotcha'

select * from deleteMe2 where data = 'Gotcha'

drop table deleteMe2

GO

update systypes

set collationid = 49160

where name = 'tryThis'

GO