|
|
Posted by David Portas on 10/02/53 11:47
Nasir wrote:
> Hi there,
>
> Is it posible to set the collation of SQL server 2005 like Oracle, which is
> that objects names and column names are case-insensitive, but data is
> sensitive;e.g:
>
> In Oracle:
> --create lower-case table and column name
> create table t1(c1 char(10));
> --Mike with upper-case J
> insert into t1 values('Mike');
> --follwoing I'm changing case in the table name, but it works, however
> 'Mike' has to be with uppewr case M
> select * from T1 where c1='Mike'
>
> Above test fails in SQL server. Is there a collation which can make data
> 'literals' sensitive, but data dictionary or the object names and column
> names case insensitive?
>
> TIA,
> Nasir
Yes. In fact the collation for data is always determined at column
level. The database collation defines whether identifiers are
case-sensitive and is also the default for the column collation. Take a
look at the collations topics in Books Online.
CREATE TABLE t1(c1 CHAR(10) COLLATE Latin1_General_CS_AS NOT NULL /*
.... */);
INSERT INTO t1 VALUES('Mike');
INSERT INTO t1 VALUES('mike');
SELECT * FROM T1 WHERE c1='Mike';
Result:
c1
----------
Mike
(1 row(s) affected)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|