• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

如何将sqlserver 2005设置为大小写不敏感

xuxingguang 2007-10-18 02:24:38
公司里有一些脚本文件,程序里面没有区分大小写。请问各位,如何将sqlserver 2005设置为大小写不敏感?这样就可以执行这些脚本文件了。

先谢谢大家了!
...全文
1197 点赞 收藏 4
写回复
4 条回复
中国风 2007年10月20日
如果安装时没有定义时排序规则时,数据库默认是不区分大小写的,在创表时也可定义排序规则。。如果都没定义时,可用以下方法实现:


_BIN 二进制排序
_CI_AI 不区分大小写、不区分重音、不区分假名类型、不区分宽度
_CI_AI_WS 不区分大小写、不区分重音、不区分假名类型、区分宽度
_CI_AI_KS 不区分大小写、不区分重音、区分假名类型、不区分宽度
_CI_AI_KS_WS 不区分大小写、不区分重音、区分假名类型、区分宽度
_CI_AS 不区分大小写、区分重音、不区分假名类型、不区分宽度
_CI_AS_WS 不区分大小写、区分重音、 不区分假名类型、区分宽度
_CI_AS_KS 不区分大小写、区分重音、区分假名类型、不区分宽度
_CI_AS_KS_WS 不区分大小写、区分重音、区分假名类型、区分宽度
_CS_AI 区分大小写、不区分重音、 不区分假名类型、不区分宽度
_CS_AI_WS 区分大小写、不区分重音、不区分假名类型、区分宽度
_CS_AI_KS 区分大小写、不区分重音、区分假名类型、不区分宽度
_CS_AI_KS_WS 区分大小写、不区分重音、区分假名类型、区分宽度
_CS_AS 区分大小写、区分重音、不区分假名类型、不区分宽度
_CS_AS_WS 区分大小写、区分重音、不区分假名类型、区分宽度
_CS_AS_KS 区分大小写、区分重音、区分假名类型、不区分宽度
_CS_AS_KS_WS 区分大小写、区分重音、区分假名类型、区分宽度
*/
create table ta(id int ,name varchar(20))
insert ta
select 1, 'A' union all
select 2, 'A'union all
select 3, 'a,' union all
select 4, 'A,'


select * from ta where name like'A%'--所有记录都会在结果集
select * from ta where name collate Chinese_PRC_CS_AS_WS like '%,%'--只有第4条记录满足
select * from ta where name collate Chinese_PRC_CS_AS_WS like'a%'--第3条记录满足(加_ws与上一方法结果相同)
select * from ta where name collate Chinese_PRC_CS_AS_WS like'A%'--第2条记录满足
select * from ta where name collate Chinese_PRC_BIN like'A'--第1条记录满足(用二进制排序结果同上相同)

结果如下:
id name
----------- --------------------
1 A
2 A
3 a,
4 A,

(所影响的行数为 4 行)

id name
----------- --------------------
4 A,

(所影响的行数为 1 行)

id name
----------- --------------------
3 a,

(所影响的行数为 1 行)

id name
----------- --------------------
2 A

(所影响的行数为 1 行)

id name
----------- --------------------
1 A

(所影响的行数为 1 行)

--drop table ta

回复 点赞
zhou__zhou 2007年10月18日
try :
USE [master]
GO
ALTER DATABASE [youDBName] COLLATE Chinese_PRC_CI_AI
GO

注意:此语句不能更改任何现有用户定义的表中列的排序规则
回复 点赞
xuxingguang 2007年10月18日
什么意思啊?不了解,给详细说说好吗?
回复 点赞
Generics 2007年10月18日
/*
Background : SQL2000 supports collation at different levels.
Collation can be based on the Windows Collation or SQL Collation.
Collation can be specified at the server, database & column level.
For more details, see the BOL topics on COLLATION. So this small
article should give a fairly detailed view of the various options
available & gotchas! COLLATION is very powerful but requires good
understanding to use them efficiently.
*/
-- To determine all the available collations use the system function
-- fn_helpcollations. The example below gets all the SQL Latin collations.
SELECT * FROM ::fn_helpcollations()
WHERE [name] LIKE 'SQL_Latin%';
/*
name description
------------------------------- ---------------------------------------------------------------
SQL_Latin1_General_CP437_BIN Latin1-General, binary sort for Unicode Data,
SQL Server Sort Order 30 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_CP437_CI_AI Latin1-General, case-insensitive, accent-insensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 34 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_CP437_CI_AS Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 32 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_CP437_CS_AS Latin1-General, case-sensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 31 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_CP850_BIN Latin1-General, binary sort for Unicode Data,
SQL Server Sort Order 40 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_CP850_CI_AI Latin1-General, case-insensitive, accent-insensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 44 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_CP850_CI_AS Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 42 on Code Page 850 for non-Unicode Data
*/
GO

-- To determine the server-wide collation established during SETUP do:
SELECT SERVERPROPERTY( 'Collation' ) AS Server_Default_Collation;
/*
Server_Default_Collation
------------------------------
SQL_Latin1_General_CP437_CI_AS
*/
GO

-- Now let's create a database with a different collation
-- than the server's i.e., the SQL Latin with binary sort order
CREATE DATABASE CollateTestDB COLLATE SQL_Latin1_General_CP850_BIN;
GO
USE CollateTestDB;
GO
-- To check the database collation do:
SELECT DATABASEPROPERTYEX( 'CollateTestDB' , 'Collation' ) AS Database_Default_Collation
/*
Database_Default_Collation
----------------------------
SQL_Latin1_General_CP850_BIN
*/
GO

-- Default table creation behavior
CREATE TABLE Tbl1 (
Col1 char( 10 ) NOT NULL ,
Col2 varchar( 10 ) NULL
);
INSERT INTO Tbl1 VALUES( 'a' , 'b' );
INSERT INTO Tbl1 VALUES( 'A' , 'B' );
GO
/* Column properties from "EXEC sp_help 'Tbl1';" output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP850_BIN
Col2 varchar 10 SQL_Latin1_General_CP850_BIN
*/
GO
-- To verify binary sort order / collation
SELECT * FROM Tbl1 WHERE Col1 = 'a' And Col2 = 'b';
-- Expected output:
/*
Col1 Col2
---------- ----------
a b
*/
GO
-- To force a case-sensitive check
SELECT * FROM Tbl1
WHERE Col1 COLLATE SQL_Latin1_General_CP850_CI_AI = 'a' And
Col2 COLLATE SQL_Latin1_General_CP850_CI_AI = 'b';
-- Expected output:
/*
Col1 Col2
---------- ----------
a b
A B
*/

-- Create a table with columns having case-sensitive collation
CREATE TABLE Tbl2 (
Col1 char( 10 ) COLLATE SQL_Latin1_General_CP850_CS_AS NOT NULL ,
Col2 varchar( 10 ) COLLATE SQL_Latin1_General_CP850_CI_AI NULL
);
INSERT INTO Tbl2 VALUES( 'a' , 'b' );
INSERT INTO Tbl2 VALUES( 'A' , 'B' );
GO
/* Column properties from "EXEC sp_help 'Tbl2';" output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP850_CS_AS
Col2 varchar 10 SQL_Latin1_General_CP850_CI_AI
*/
GO

-- Some sample SELECT statements:
SELECT * FROM Tbl2 WHERE Col1 = 'a';
/*
Col1 Col2
---------- ----------
a b
*/
SELECT * FROM Tbl2 WHERE Col2 = 'b';
/*
Col1 Col2
---------- ----------
a b
A B
*/

-- Now let's take this problem a step further & create a temporary table
-- using Tbl1 by performing a SELECT INTO. Notice that the columns are
-- created with the collation of the columns of Tbl1.
SELECT * INTO #Tbl1 FROM Tbl1;
EXEC( 'Use Tempdb EXEC sp_help ''#Tbl1''');
DROP TABLE #Tbl1;
/* Column properties from output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP850_BIN
Col2 varchar 10 SQL_Latin1_General_CP850_BIN
*/
Go


-- Similarly , to create a table using SELECT INTO with a different
-- collation for the columns, do:
SELECT Col1 COLLATE SQL_Latin1_General_CP437_CI_AS AS Col1,
Col2 COLLATE SQL_Latin1_General_CP437_CI_AS AS Col2
INTO #Tbl1 FROM Tbl1;
EXEC( 'Use Tempdb EXEC sp_help ''#Tbl1''');
DROP TABLE #Tbl1;
/* Column properties from sp_help output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP437_CI_AS
Col2 varchar 10 SQL_Latin1_General_CP437_CI_AS
*/
Go

-- Now do the same by creating a temporary table using CREATE TABLE statement.
-- The created table will use the default of tempdb & not the current user db.
CREATE TABLE #Tbl1 (
Col1 char( 10 ) NOT NULL ,
Col2 varchar( 10 ) NULL
);
EXEC( 'Use Tempdb EXEC sp_help ''#Tbl1''');
DROP TABLE #Tbl1;
/* Column properties from output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP437_CI_AS
Col2 varchar 10 SQL_Latin1_General_CP437_CI_AS
*/
Go

/*
To force the creation of the temporary table with the current user database
collation default, use the DATABASE_DEFAULT clause with COLLATION like:
*/
CREATE TABLE #Tbl1 (
Col1 char( 10 ) COLLATE database_default NOT NULL ,
Col2 varchar( 10 ) COLLATE database_default NULL
);
EXEC( 'Use Tempdb EXEC sp_help ''#Tbl1''');
DROP TABLE #Tbl1;
/* Column properties from output is shown below:

Column_name Type Length Collation
----------- ------- ------ ----------------------------
Col1 char 10 SQL_Latin1_General_CP850_BIN
Col2 varchar 10 SQL_Latin1_General_CP850_BIN
*/
Go

-- Now we are all set, drop the test database
USE master;
DROP DATABASE CollateTestDB;
GO

回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告