关于sql server 中的排序规则的疑问

Question

利用排序规则解决特殊字符的查找问题
http://topic.csdn.net/u/20080317/17/55e464a2-f40a-43a8-9f94-21be297a52fb.html?1084261463select CharIndex('_@_',N'RoleName_@_゛戀戰℡帝王') 
为什么这个输出不是9,而是0啊.而且 
select CharIndex('_@_',N'RoleName_@_戀戰℡帝王')这样就是正常的输出9,两种就是多了一个特殊字符而已啊 -------------------------
select
    CharIndex('_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE Chinese_PRC_BIN),
    CharIndex('_@_',N'RoleName_@_戀戰℡帝王' COLLATE Chinese_PRC_BIN)select
    CharIndex('_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE SQL_Latin1_General_CP437_BIN),
    CharIndex('_@_',N'RoleName_@_戀戰℡帝王' COLLATE SQL_Latin1_General_CP437_BIN)--> 查找排序规则：
select top 1 * from ::fn_helpcollations() where name like '%BIN'
--> 二进制排序是区分大小写的：
select
    CharIndex('e_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE Chinese_PRC_BIN),
    CharIndex('E_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE Chinese_PRC_BIN)--> 如果要不区分大小写：
select
    CharIndex('e_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE Chinese_PRC_CI_AI),
    CharIndex('E_@_',N'RoleName_@_゛戀戰℡帝王' COLLATE Chinese_PRC_CI_AI)--> 查找排序规则：
select top 1 * from ::fn_helpcollations() where name like '%CI%AI%'
在各种处理中应用排序规则的示例
--转自邹键--1. 为数据库指定排序规则
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GOALTER DATABASE db COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--2. 为表中的列指定排序规则
CREATE TABLE tb(
col1 varchar(10),
col2 varchar(10) COLLATE Chinese_PRC_CI_AS)
GOALTER TABLE tb ADD col3 varchar(10) COLLATE Chinese_PRC_BIN
GOALTER 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排序规则在拼音处理中的应用--1. 按拼音排序
DECLARE @t TABLE(col varchar(2))
INSERT @t SELECT '中'
UNION ALL SELECT '国'
UNION ALL SELECT '人'SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS
/*--结果
col  
---- 
国
人
中
--*/
GO
/*==========================================*/
--2. 汉字首字母查询处理用户定义函数
CREATE FUNCTION f_GetPY(@str nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @py TABLE(
ch char(1),
hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,
hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)
INSERT @py SELECT 'A',N'吖',N'鏊'
UNION  ALL SELECT 'B',N'八',N'簿'
UNION  ALL SELECT 'C',N'嚓',N'错'
UNION  ALL SELECT 'D',N'哒',N'跺'
UNION  ALL SELECT 'E',N'屙',N'贰'
UNION  ALL SELECT 'F',N'发',N'馥'
UNION  ALL SELECT 'G',N'旮',N'过'
UNION  ALL SELECT 'H',N'铪',N'蠖'
UNION  ALL SELECT 'J',N'丌',N'竣'
UNION  ALL SELECT 'K',N'咔',N'廓'
UNION  ALL SELECT 'L',N'垃',N'雒'
UNION  ALL SELECT 'M',N'妈',N'穆'
UNION  ALL SELECT 'N',N'拿',N'糯'
UNION  ALL SELECT 'O',N'噢',N'沤'
UNION  ALL SELECT 'P',N'趴',N'曝'
UNION  ALL SELECT 'Q',N'七',N'群'
UNION  ALL SELECT 'R',N'蚺',N'箬'
UNION  ALL SELECT 'S',N'仨',N'锁'
UNION  ALL SELECT 'T',N'他',N'箨'
UNION  ALL SELECT 'W',N'哇',N'鋈'
UNION  ALL SELECT 'X',N'夕',N'蕈'
UNION  ALL SELECT 'Y',N'丫',N'蕴'
UNION  ALL SELECT 'Z',N'匝',N'做'
DECLARE @i int
SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
FROM @py
WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2
RETURN(@str)
END
GO

Answer 1

http://www.cnblogs.com/starluck/articles/1968883.html楼主看看这个资料；
另外可指定的排序有：order by col asc--按col字段增排序
order by col desc--按col字段减排序

Answer 2

排序规则在全角与半角处理中的应用--1．查询区分全角与半角字符
--测试数据
DECLARE @t TABLE(col varchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'ＡＡ'   --全角Ａ
UNION ALL SELECT 'Ａ,Ａ' --全角Ａ,半角逗号(,)
UNION ALL SELECT 'Ａ，Ａ' --全角Ａ,全角逗号(，)--1.查大写字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'--2.查全角字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%Ａ%'--3.查半角逗号(,)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%'--3.查全角逗号(，)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%，%'
GO
/*=============================================*/
--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit              --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N'　',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N'　')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
数据库查看和更改排序规则及相关应用查询Sql Server默认排序规则
sp_helpsort SELECT SERVERPROPERTY ('Collation')修改排序规则
表
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 Server 2005
重建master数据库
start /wait setup.exe /qb INSTANCENAME=<实例名> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<密码> SQLCOLLATION=Chinese_PRC_CI_AS---------------------------------------------------------1. 为数据库指定排序规则
CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
GOALTER DATABASE db COLLATE Chinese_PRC_BIN
GO
/*====================================*/
--2. 为表中的列指定排序规则
CREATE TABLE tb(
col1 varchar(10),
col2 varchar(10) COLLATE Chinese_PRC_CI_AS)
GOALTER TABLE tb ADD col3 varchar(10) COLLATE Chinese_PRC_BIN
GOALTER 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
------------------------------------------------------------表
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   tbdrop   table   tb指定排序规则即可Windows   排序规则名称  
在   COLLATE   子句中指定   Windows   排序规则名称。Windows   排序规则名称由排序规则指示器和比较风格构成。  语法  
<   Windows_collation_name   >   ::   =          CollationDesignator_ <ComparisonStyle>        <   ComparisonStyle   >   ::=  
                CaseSensitivity_AccentSensitivity  
                [_KanatypeSensitive   [_WidthSensitive   ]   ]  
                |   _BIN  参数  
CollationDesignator  指定   Windows   排序规则使用的基本排序规则。基本排序规则包括：  当指定按字典排序时应用其排序规则的字母表或语言
用于存储非   Unicode   字符数据的代码页。  
例如   Latin1_General   或法文，两者都使用代码页   1252，或土耳其文，它使用代码页   1254。  CaseSensitivityCI   指定不区分大小写，CS   指定区分大小写。AccentSensitivityAI   指定不区分重音，AS   指定区分重音。KanatypeSensitiveOmitted   指定不区分大小写，KS   指定区分假名类型。WidthSensitivityOmitted   指定不区分大小写，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)) 

Answer 3

SQL Server排序规则(collation)冲突和解决方案
什么是排序规则（collation）
 关于SQL Server的排序规则，估计大家都不陌生，在创建数据库时我们经常要选择一种排序规则（conllation），一般我们会留意到每一种语言的排序规则都有许多种，比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多这些排序规则有什么作用呢？让我们先来看看MS官方的解释：排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则具有下面的特征：语言 
区分大小写 
区分重音 
区分假名
比如在SQL Server 2005中，排序规则名称由两部份构成，比如 Chinese_PRC_CI_AI_WS
前半部份是指本排序规则所支持的字符集，如Chinese_PRC 指针对大陆简体字UNICODE的排序规则。
后半部份即后缀的含义如下： _BIN               
 指定使用向后兼容的二进制排序顺序。 
_BIN2      指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。 
_Stroke   按笔划排序 
_CI(CS) 是否区分大小写，CI不区分，CS区分 
_AI(AS) 是否区分重音，AI不区分，AS区分 
_KI(KS) 是否区分假名类型，KI不区分，KS区分 
_WI(WS) 是否区分全半角，WI不区分，WS区分  既然排序规则如此复杂，那么应用了不同排序规则的列之间默认情况下便不能进行Union、Join、Like等equal操作了，于是便有了排序规则（collation）冲突。 排序规则（collation）冲突
 我们知道，SQL Server 从2000 开始，便支持多个排序规则。SQL Server 2000 的数据库可使用除默认排序规则以外的其他排序规则。此外，SQL Server 2000 还支持为列专门制定排序规则。这样一来，我们在写跨表、跨数据库、跨服务器操作的T-SQL时，如果equal的字段排序规则不同，便会发生排序规则冲突。比如我们先见两个结构相同的表，但字段的排序规则不同：-- 1. Create TableA.CREATE TABLETagsTableA(TagNameNVARCHAR(64)COLLATEChinese_PRC_BIN)-- 2. Create TableB.CREATE TABLETagsTableB(TagNameNVARCHAR(64)COLLATEChinese_PRC_CI_AS)当表建好之后执行：-- 3. Try to join themSELECT*fromTagsTableA AINNER JOINTagsTableB BonA.TagName=B.TagName便会出下类似下面的问题：无法解决 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。 常见的场景——临时表
 我们知道，SQL Server的临时表是保存在Tempdb数据库中的。而使用临时表的数据库与临时表的排序规则（conllation）不一定相同。所以，当Tempdb的排序规则与当前使用临时表的数据库排序规则不同时，便会出现排序规则冲突。一般来说，我们在创建临时表时可能不会注意到排序规则，从而留下排序规则冲突的隐患。比如Openlab V4.0的Blog模块中的一个存储过程，便有着这种隐患：/****** 对?象ó:  StoredProcedure [blogs].[up_CreateGetTagIds]    脚本日期: 01/20/2010 19:10:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*RETURN VALUES:Ids*/-- =============================================-- Author:        <Lance Zhang>-- Create date: <2010-01-06>-- Description: <Make sure all the tag EXISTS in DB, and then get their ids.>-- 1. Create Temp Table.-- 2. Insert TagNames into Temp Table.-- 3. Add new Tags to [Categories] from query Temp Table.-- 4. Batch Get All Tag Ids from [Categories].-- 5. Clear and drop Temp Table.-- =============================================ALTER PROCEDURE[blogs].[up_CreateGetTagIds](@BlogIdINT,@TagNamesXML)ASBEGIN/******************************* SET CONFIG *************************************************/SET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NUMERIC_ROUNDABORT OFF/******************************* DECLARE VARIABLE *************************************************//********************************BEGIN TRANSATION**********************************************/BEGIN TRYBEGIN TRANSACTION;-- 1. Create Temp Table.CREATE TABLE#TagsTable(TagNameNVARCHAR(64))-- 2. Insert TagNames into Temp Table.INSERT INTO#TagsTableSELECTTG.Tags.value('@i','NVARCHAR(64)')[email protected]('/ts/t')TG(Tags)-- 3. Add new Tags to [Categories] from query Temp Table.BEGININSERT INTO[Categories]([BlogId],[ParentId],[CategoryType],[CategoryName],[LoweredCategoryName],[Slug],[LoweredSlug],[Description],[CreatedDateUtc],[TotalEntities],[SortOrder],[State])SELECT@BlogId,0,-- ParentId, 0 as default.2,-- CategoryType, 2 as Post Tag.TT.TagName,LOWER(TT.TagName),TT.TagName,-- Slug, use CategoryName as default.LOWER(TT.TagName),-- LoweredSlug, use LoweredCategoryName as default.'',-- Description, Empty as default.GETUTCDATE(),0,-- TotalEntities, 0 as default.1,-- SortOrder of PostTags can always be 1.1-- State, 1 as Normal.FROM#TagsTable TTWHERELOWER(TT.TagName) NOT IN(SELECTC.[LoweredCategoryName]FROM[Categories] CWITH(UPDLOCK,HOLDLOCK)WHERE[BlogId]=@BlogIdAND[CategoryType]=2-- Post Tag.)END-- 4. Batch Get All Tag Ids from [Categories].BEGINSELECT[CategoryId]FROM[Categories] CWITH(NOLOCK)JOIN#TagsTable TTONC.[LoweredCategoryName]=LOWER(TT.TagName)WHEREC.[BlogId]=@BlogIdANDC.[CategoryType]=2-- Post Tag.ANDC.[State]=1-- 1 as Normal status.END-- 5. Clear and drop Temp Table.TRUNCATE TABLE#TagsTableDROP TABLE#TagsTableCOMMIT TRANSACTION;RETURN1END TRYBEGIN CATCHIFXACT_STATE() <>0BEGINROLLBACK TRANSACTION;RETURN-1ENDEND CATCHENDGO常见的解决方案
 知道了什么是排序规则冲突，我们接下来分析冲突的解决方案，以数据库级别的排序规则为例，一般来说，解决方案有下面几种把SQL实例删了重建 ——大多数情况下等于没说-_-||| 
修改数据库的排序规则 ——参考阿牛兄的这篇文章 
在T-SQL中使用COLLATEDATABASE_DEFAULT来解决冲突 ——接下来主要讨论这个
 COLLATE DATABASE_DEFAULT
 CollateXXX操作可以用在字段定义或使用时，它会将字段定义或转换成XXX的排序规则格式。而CollateDatabase_Default则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。比如在下面的代码中便使用了CollateDatabase_Default来解决字段在equal操作中的排序规则冲突：Insert into Security.Report(Name)SelectC.Path FromSSRS.CatalogCWhereC.PathCollateDatabase_DefaultLike@ReportPath+'/%'AndC.PathCollateDatabase_DefaultNot In (SelectNameFrom Security.Report R)当然，在创建临时表时若对字段定义加上CollateDatabase_Default，也可以方便地解决潜在的排序规则冲突，比如上一节中提到的存储过程，只要做如下修改即可。-- 1. Create Temp Table.CREATE TABLE#TagsTable(TagNameNVARCHAR(64)COLLATEDATABASE_DEFAULT)结束语
对于专业的SQLer来说，排序规则的应用场景还有很多，例如利用排序规则特点计算汉字笔划和取得拼音首字母等等，更多信息，请查阅MSDN文档：http://msdn.microsoft.com/zh-cn/library/aa258237(en-us,SQL.80).aspx

Answer 4

这个关于排序规则的挺详细的
但我只想知道 负号 -   有什么特殊意义吗？我看字符中有这个符号时有些诡异比如
-  <  1
3
-a > 1a
a1 <  -a2  < a3这是啥原因？用的是Chinese_PRC_CI_AS
中文sql server的默认排序规则if  '-' < '1'  print '''-'' < ''1'''
if '-a' < '1a' print '1'
if '-a' = '1a' print '2'
if '-a' > '1a' print '3'
if '-a' > '1a' print '''-a'' > ''1a'''
 
drop table #test
create table #test (test varchar(20) )
insert #test 
    select '-1147721273' 
    union all select  '1147721284'
    union all select  '114772125'
    union all select  'a1'
    union all select  'a3'
    union all select  '-a2'
    union all select  'a-2'
    union all select  'a-4'
    union all select  'a4'
 
     
select * from #test order by 1