1.sp_helpsort
SELECT SERVERPROPERTY ('Collation')
æ¥çä½ çæåºè§å.
ä¸è¿ä½ çè¿ä¸ªåºè¯¥åå符éæå
³.
2.æ´æ¹æå¡å¨æåºè§å
æ´æ¹ SQL Server 2005 å®ä¾çé»è®¤æåºè§åçæä½å¯è½ä¼æ¯è¾å¤æï¼å
æ¬ä»¥ä¸æ¥éª¤ï¼
ç¡®ä¿å
·æéæ°å建ç¨æ·æ°æ®åºåè¿äºæ°æ®åºä¸çææ对象æéçå
¨é¨ä¿¡æ¯æèæ¬ã
使ç¨å·¥å
·ï¼ä¾å¦å¤§å®¹éå¤å¶ï¼å¯¼åºæææ°æ®ã
å é¤ææç¨æ·æ°æ®åºã
éæ°çæå¨ setup å½ä»¤ç SQLCOLLATION å±æ§ä¸æå®æ°çæåºè§åç master æ°æ®åºãä¾å¦ï¼
å¤å¶ä»£ç
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
æå
³éæ°çæ master æ°æ®åºç详ç»ä¿¡æ¯ï¼è¯·åé
å¦ä½éæ°çæ SQL Server 2005 ç Master æ°æ®åºã
å建æææ°æ®åºåè¿äºæ°æ®åºä¸çææ对象ã
导å
¥æææ°æ®ã
注æï¼
å¯ä»¥ä¸ºå建çæ¯ä¸ªæ°æ°æ®åºæå®é»è®¤æåºè§åï¼èä¸æ´æ¹ SQL Server 2005 å®ä¾çé»è®¤æåºè§åã
3.设置åæ´æ¹æ°æ®åºæåºè§å
å建æ°æ°æ®åºæ¶ï¼å¯ä»¥ä½¿ç¨ä¸åå
容ä¹ä¸æå®æåºè§åï¼
CREATE DATABASE è¯å¥ç COLLATE åå¥ã
SQL Server Management Studio.
SQL 管ç对象 (SMO) ä¸ç Database.Collation å±æ§ã
å¦ææªæå®æåºè§åï¼å使ç¨æå¡å¨æåºè§åã
å¯ä»¥ä½¿ç¨ ALTER DATABASE è¯å¥ç COLLATE åå¥æ¥æ´æ¹å¨ç¨æ·æ°æ®åºä¸å建çä»»ä½æ°å¯¹è±¡çæåºè§åã使ç¨æ¤è¯å¥ä¸è½æ´æ¹ä»»ä½ç°æç¨æ·å®ä¹ç表ä¸åçæåºè§åãä½¿ç¨ ALTER TABLE ç COLLATE åå¥å¯ä»¥æ´æ¹è¿äºåçæåºè§åã
æ´æ¹æ°æ®åºæåºè§åæ¶ï¼éè¦æ´æ¹ä¸åå
容ï¼
æ°æ®åºçé»è®¤æåºè§åï¼è¿ä¸æ°çé»è®¤æåºè§åå°åºç¨äºæ°æ®åºä¸åç»å建çææåãç¨æ·å®ä¹çæ°æ®ç±»åãåéååæ°ãæ ¹æ®æ°æ®åºä¸å®ä¹ç对象解æ SQL è¯å¥ä¸æå®ç对象æ è¯ç¬¦æ¶ï¼ä¹ä½¿ç¨æ°çé»è®¤æåºè§åã
å°ç³»ç»è¡¨ä¸çä»»ä½ charãvarcharãtextãncharãnvarchar æ ntext åæ´æ¹ä¸ºä½¿ç¨æ°çæåºè§åã
å°åå¨è¿ç¨åç¨æ·å®ä¹å½æ°çææç°æ charãvarcharãtextãncharãnvarchar æ ntext åæ°åæ éè¿åå¼æ´æ¹ä¸ºä½¿ç¨æ°çæåºè§åã
å° charãvarcharãtextãncharãnvarchar æ ntext ç³»ç»æ°æ®ç±»åååºäºè¿äºç³»ç»æ°æ®ç±»åçææç¨æ·å®ä¹çæ°æ®ç±»åæ´æ¹ä¸ºä½¿ç¨æ°çé»è®¤æåºè§åã
SQL code :
1.å°æ°æ®åºçå符éä¿®æ¹ä¸ºï¼
alter database dbname collate Chinese_PRC_CI_AS
2.
--1. 为æ°æ®åºæå®æåºè§å
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GO
ALTER DATABASE db COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--2. 为表ä¸çåæå®æåºè§å
CREATE TABLE tb(
col1 varchar(10),
col2 varchar(10) COLLATE Chinese_PRC_CI_AS)
GO
ALTER TABLE tb ADD col3 varchar(10) COLLATE Chinese_PRC_BIN
GO
ALTER TABLE tb ALTER COLUMN col2 varchar(10) COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--3. 为å符åéååæ°åºç¨æåºè§å
DECLARE @a varchar(10),@b varchar(10)
SELECT @a='a',@b='A'
--使ç¨æåºè§å Chinese_PRC_CI_AS
SELECT CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' ELSE '@a<>@b' END
--ç»æ:@a=@b
--使ç¨æåºè§å Chinese_PRC_BIN
SELECT CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b' ELSE '@a<>@b' END
--ç»æ:@a<>@b
3.
表
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS
--ä¸åºå大å°å
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CS_AS
--åºå大å°å
æ°æ®åº
ALTER DATABASE database
COLLATE Chinese_PRC_CS_AS
--åºå大å°å
ALTER DATABASE database COLLATE Chinese_PRC_CI_AS --ä¸åºå大å°å
æ¹æ³ä¸.å®è£
SQLæ¶éæ©åºå大å°å
æå®è£
å®ä»¥åé建mastarï¼éæ©åºå大å°
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\rebuildm.exe
æ¹æ³äº.sql server 8.0以ä¸ççæ¬æå¯ä»¥ï¼7.0åå
¶ä»¥ä¸ä¸æ¯æ
alter database æ°æ®åº COLLATE Chinese_PRC_CS_AS
ä¿®æ¹æåºè§åï¼æ¹æ大å°åææçæåºè§å
å¦æåªä¿®æ¹ä¸ä¸ªè¡¨ï¼ç¨alter tableè¯å¥
å¦æä¿®æ¹ä¸ä¸ªåºçé»è®¤æåºè§åï¼ç¨alter datebaseè¯å¥
å¦æä¿®æ¹æ´ä¸ªæå¡å¨çé»è®¤æåºè§åï¼ç¨Rebuildm.exeé建masteråº
--æå®æåºè§åå°±å¯ä»¥äº
--示ä¾
select replace('AbacB' collate Chinese_PRC_CS_AS_WS,'B','test')
--å¦æä½ æ¯è¦æ±è¡¨æ¯æ,åå¯ä»¥å»ºè¡¨æ¶æå®æåºè§å,è¿æ ·replaceå°±ä¸ç¨åæåºè§åäº
--示ä¾
create table tb(a varchar(20) collate Chinese_PRC_CS_AS_WS)
insert tb values('Abac')
select replace(a,'a','test') from tb
drop table tb
æå®æåºè§åå³å¯
Windows æåºè§åå称
å¨ COLLATE åå¥ä¸æå® Windows æåºè§åå称ãWindows æåºè§åå称ç±æåºè§åæ示å¨åæ¯è¾é£æ ¼ææã
è¯æ³
< Windows_collation_name > :: =
CollationDesignator_ <ComparisonStyle>
< ComparisonStyle > ::=
CaseSensitivity_AccentSensitivity
[_KanatypeSensitive [_WidthSensitive ] ]
| _BIN
åæ°
CollationDesignator
æå® Windows æåºè§å使ç¨çåºæ¬æåºè§åãåºæ¬æåºè§åå
æ¬ï¼
å½æå®æåå
¸æåºæ¶åºç¨å
¶æåºè§åçåæ¯è¡¨æè¯è¨
ç¨äºåå¨é Unicode å符æ°æ®ç代ç 页ã
ä¾å¦ Latin1_General ææ³æï¼ä¸¤è
é½ä½¿ç¨ä»£ç 页 1252ï¼æåè³å
¶æï¼å®ä½¿ç¨ä»£ç 页 1254ã
CaseSensitivity
CI æå®ä¸åºå大å°åï¼CS æå®åºå大å°åã
AccentSensitivity
AI æå®ä¸åºåéé³ï¼AS æå®åºåéé³ã
KanatypeSensitive
Omitted æå®ä¸åºå大å°åï¼KS æå®åºåååç±»åã
WidthSensitivity
Omitted æå®ä¸åºå大å°åï¼WS æå®åºå大å°åã
BIN
æå®ä½¿ç¨äºè¿å¶æåºæ¬¡åºã
å¦æä½ åªæ¯ç®åæ¥è¯¢åºåï¼é£ä¹è¿æ¯ä¸è¦è¿æ ·æ¹ï¼å
å¾ååæï¼å¦æ¤æ¥è¯¢ï¼
select * from a
/*
a_nam a_add
---------- ----------
1 aa
1 bb
2 cc
2 vv
2 kk
3 dd
3 ee
4 dd
5 ee
6 yy
6 yy
(11 row(s) affected)
*/
ç°å¨æ们æ¥è¯¢a_add = 'aa'çï¼'Aa'ççä¸è¡ï¼
Example 1ï¼
select * from a
where a_add collate Chinese_PRC_CS_AS_WS = 'aa'
/*
a_nam a_add
---------- ----------
1 aa
(1 row(s) affected)
*/
Example 2ï¼
select * from a
where a_add collate Chinese_PRC_CS_AS_WS = 'Aa'
/*
a_nam a_add
---------- ----------
(0 row(s) affected)
*/
æ¹æ³ä¸.ä¸é¢çè®°ä¸ä½ï¼é£ä¹å°±ç¨æ笨çæ¹æ³ï¼è½¬å为ascii
select * from a
where
ascii(substring(a_add,1,1)) = ascii(substring('Aa',1,1))
and
ascii(substring(a_add,2,1)) = ascii(substring('Aa',2,1))
/*
a_nam a_add
---------- ----------
(0 row(s) affected)
*/
æ¹æ³ä¸ï¼ä»»ä½çæ¬é½å¯ä»¥
select * from a
where cast(a_add as varbinary(10))= cast('aa' as varbinary(10))
温馨提示:答案为网友推荐,仅供参考