SQL 2005字符转换问题

[tushadongjing]

最近查询一个数据库发现中文字符在查询分析器乱码，因为我使用的是SQL 2005的SQL Server Management Studio,
怎么也解决不了查看查询结果乱码的问题，请各位指点.

但是在SQL 2000中，可以将Tools -> Options -> Connections下将
Perform translation for character data的选择项取消就可以看到中文了.

是否SQL 2005就没提供这个功能

另外如果通过外部程序读取,如果使用SQLConnection无论怎样中文都是乱码,非得用OLEDB或ODBC且设置不转换字符才
可以不乱码.

[htl258_Tony]

在SQL 2005中用T-SQL插入中文数据时出现的问号或乱码的解决方案！！！！ 收藏

< type="text/javascript"> 
病症表现为：主要表现为用T-sql语句插入中文数据时数据库显示全是问号“？？？” 
解决办法：

第一种办法：先选中出错的数据库→选中以后右键点击属性会弹出数据库属性 对话框→选中数据库属性对话框中的选项→把选项中的排序规则设置成:Chinese_PRC_90_CI_AS→最后点击确定即可。
（注意：在选择数据库属性的时候必须确保你所修改的数据库未被使用才可以修改否则会失败的）

第二种办法：首先打开你的sql查询分析器（新建查询），然后输入以下代码执行即 可：ALTER DATABASE 数据库名 COLLATE Chinese_PRC_90_CI_AS
例如： 
ALTER DATABASE 9NPC COLLATE Chinese_PRC_CI_AIALTER DATABASE AutoUpgrade COLLATE Chinese_PRC_CI_AI

解析原因：出现以上情况主要是因为数据库属性的排序规则设置不正确，在Microsoft SQL Server 2005 中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。
排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：
　　Chinese_PRC_90_CI_AS
前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义： 
　　_BIN 二进制排序 
　　_CI(CS) 是否区分大小写，CI不区分，CS区分
　　_AI(AS) 是否区分重音，AI不区分，AS区分　　　
　　_KI(KS) 是否区分假名类型&defKI不区分，KS区分　
_WI(WS) 是否区分宽度 WI不区分，WS区分　

区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。
区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，比较还将重音不同的字母视为不等。
区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项（完）。

******************************************************************************************




本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/03/4007035.aspx

[SQL77]

排序规则整一下,还有插入是加N'',字段改成NVARCHAR,一般没问题了

[tushadongjing]

我没有说要改字符排序,我只是想看看而已,因为在SQL 2000中将Tools -> Options -> Connections下的
Perform translation for character data的选择项取消后,看到中文就没有问题了.

我想问的是在2005的SQL Server Management Studio有没类似选项可以做到这一点.

如果改排序规则,对之前系统使用可能造成影响(现在之前系统一切正常),不到万不得已,不敢冒这个风险的.

[tushadongjing]

有点想不通,SQL2000都有这个选择项,为什么SQL2005就没有了.

郁闷了,我要查个带中文的非得登陆到一台装SQL 2000查询分析器的机器去看,那多麻烦.

[--小F--]

请问怎么更改排序规则呢 


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)) 

[tushadongjing]

虽然你们回答的不是我想要的,但是还是感谢你们,谢谢了!

可能我标题写得不好,我想问的是:

在SQL 2000下,可以在Query Analyzer中将Tools -> Options -> Connections下的
Perform translation for character data的选择项取消后,查看中文就没有问题了.

我想问的是在2005的SQL Server Management Studio有没类似选项可以做到这一点,我找很久都找不到.

[tushadongjing]

看来2005真的是没有了