[小技巧推荐]SQL Server数据库查询区分大小写、全半角——排序规则的应用

[AustinLuo]

献丑开始了.....

因为偶然的原因，需要在INNER JOIN联表时，让对应字段进行区分大小写的比较。而默认情况下建立的Sql Server数据库是不区分大小写的，这个需求怎么实现呢？

要实现这个需求，至少有三个操作级别来实现：

1. 数据库级别：整个数据库中的char、varchar、text、nchar、nvarchar 和 ntext 数据都区分大小写。（为描述方便，下文不再明确强调这些数据类型。）但这样做有个明显的坏处，那就是整个数据库的这些字段的比较，都要进行严格匹配。比如下面的两条Sql语句会得到完全不同的两种结果：

SELECT * FROM [TABLE] WHERE [COLUMN] LIKE ‘%KeyWord%’
SELECT * FROM [TABLE] WHERE [COLUMN] LIKE ‘%keyword%’

因为数据库在比较时会严格按照大小写来区别，这样就会导致我们在程序开发过程中难以满足十分复杂的查询需求，特别突出的例子便是关键字搜索——我们无法预知客户输入内容的大小写，我们更不能要求客户输入的关键字区分大小写。



2. 表字段级别：只对特定的表字段进行大小写区分。这样做在整体上减小了大小写限定的范围，从全数据库减小到当前指定的字段。但使用起来仍然有些局限性。



3. 查询级别：只对本次查询限定大小写区分。这也是本文重点介绍的实现方式。这样操作可以让我们不对以前的数据库做任何的修改，只对当前这次查询区分大小写，也不会影响程序中别处对这些字段的查询。

好了，上文说了三个实现级别，那具体怎么实现呢？这就要用到SQL数据库中的“排序规则”了。我们可以在数据库的属性上，设置排序规则，也可以在表设计的字段属性里设置排序规则。具体要设置什么样的排序规则，后面来说，我们先说查询级别的排序规则。下面有一段SQL片段可以演示在查询级别区分大小写的排序规则应用：

use tempdb
set nocount on
--1--
print '建立初始数据表Customer'
create table Customer(id int,uname varchar(10))
insert into Customer 
select 1,'Jim' union all
select 2,'Simith'  union all
select 3,'uonun'

select * from Customer

--2--
print '建立初始数据表Info'
create table Info(uname varchar(10),phone varchar(11))
insert into Info 
select 'JIM','13800000000' union all
select 'Ｓimith','13911111111' union all
select 'uonun','13812345678'

select * from Info

--3--
print '不区分大小写，不区分全半角'
select c.id,c.uname as 'c.uname',i.uname as 'i.uname',i.phone
from Customer as c inner join Info as i
on c.uname = i.uname

--4--
print '区分大小写，不区分全半角'
select c.id,c.uname as 'c.uname',i.uname as 'i.uname',i.phone
from Customer as c inner join Info as i
on c.uname = i.uname
collate Chinese_PRC_CS_AS

--5--
print '不区分大小写，区分全半角'
select c.id,c.uname as 'c.uname',i.uname as 'i.uname',i.phone
from Customer as c inner join Info as i
on c.uname = i.uname
collate Chinese_PRC_CI_AI_WS

--6--
print '区分大小写，区分全半角'
select c.id,c.uname as 'c.uname',i.uname as 'i.uname',i.phone
from Customer as c inner join Info as i
on c.uname = i.uname
collate Chinese_PRC_CS_AI_WS

DROP TABLE Customer
DROP TABLE Info


/*
建立初始数据表Customer
id          uname
----------- ----------
1           Jim
2           Simith
3           uonun

建立初始数据表Info
uname      phone
---------- -----------
JIM        13800000000
Ｓimith     13911111111
uonun      13812345678

不区分大小写，不区分全半角
id          c.uname    i.uname    phone
----------- ---------- ---------- -----------
1           Jim        JIM        13800000000
2           Simith     Ｓimith     13911111111
3           uonun      uonun      13812345678

区分大小写，不区分全半角
id          c.uname    i.uname    phone
----------- ---------- ---------- -----------
2           Simith     Ｓimith     13911111111
3           uonun      uonun      13812345678

不区分大小写，区分全半角
id          c.uname    i.uname    phone
----------- ---------- ---------- -----------
1           Jim        JIM        13800000000
3           uonun      uonun      13812345678

区分大小写，区分全半角
id          c.uname    i.uname    phone
----------- ---------- ---------- -----------
3           uonun      uonun      13812345678
*/

通过上面的SQL语句可以看出，我们在查询时使用COLLATE字句，指定排序规则可以影响查询结果。通过上面的图也可以看出，这个排序规则除可以区分大小写之外，还可以区分重音、假名、全半角。

结束语：

1.本文没有更深入研究排序规则的其他应用，比如对Order by的影响。
2.本文没有更深入研究在查询时对多个不同字段的影响机制。
3.示例代码另见CorePlex代码库： http://udnz.com/Code-409.htm

对于本文没深入的方面，如果各位有更多的结论或成功，不妨发出来大家讨论讨论

参考《SQL Server 2008 联机丛书》：

使用排序规则：ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_1devconc/html/61cdbb6b-3ca1-4d73-938b-22e4f06f75ea.htm

COLLATE：ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

[AustinLuo]

[Quote=引用 80 楼 feilniu 的回复:]
补充几点：

1. 排序规则影响所有的字符比较相关机制，包括比较操作符（>、>=、=等等）、ORDER BY、GROUP BY、PARTITION BY、DISTINCT、索引的顺序和唯一性。

2. SQL Server 的排序规则依次有：实例级、数据库级、字段级、表达式级。
这4个级别范围由大到小，优先级由低到高。换言之，后者若不专门说明，则采用前一级的默认设置，若专门说明，则覆盖前一级的设置。（这种规则在计算机领域非常常见，如CSS）
LZ着重说明的是表达式级。

3. 实例级的排序规则还决定了实例级标识符（如登录名和数据库名、变量、GOTO 标签和临时表的标识符）是否区分大小写。
实例级的排序规则在实例安装时指定。修改实例级的排序规则需要使用安装程序，非常麻烦。

4. 数据库级的排序规则还决定了数据库级标识符（如表、视图和列名）是否区分大小写，以及数据库中非Unicode字符串（如char/varchar/text）的字符代码页。
数据库级的排序规则通过 CREATE DATABASE 指定，通过 ALTER DATABASE 修改。

5. 数据库迁移或部署时，要确保源数据库和目标数据库的实例级/数据库级排序规则一致，否则很容易出现意想不到的问题。

6. 综上：
由于实例级和数据库级的排序规则牵涉众多，因而从经验来说，实例级、数据库级最好采用最常见、大家最熟悉的排序规则，并且在整个项目中尽量保持一致不变。
字段级排序规则偶尔会需要使用，但会影响到该字段的所有比较操作，慎重。
数据库编程中的特殊比较需求（如区分大小写、全半角等），通常通过表达式级排序规则来实现。
如果要处理不同的排序规则，则需要了解排序规则优先顺序。详见联机丛书
[/Quote]

这才是高质量回复呀！还有更多的玉没？多抛点来~

[jakop]

很强力...

[cnkiminzhuhu]

此贴让我收获颇丰，顶

[feilniu]

补充几点：

1. 排序规则影响所有的字符比较相关机制，包括比较操作符（>、>=、=等等）、ORDER BY、GROUP BY、PARTITION BY、DISTINCT、索引的顺序和唯一性。

2. SQL Server 的排序规则依次有：实例级、数据库级、字段级、表达式级。
这4个级别范围由大到小，优先级由低到高。换言之，后者若不专门说明，则采用前一级的默认设置，若专门说明，则覆盖前一级的设置。（这种规则在计算机领域非常常见，如CSS）
LZ着重说明的是表达式级。

3. 实例级的排序规则还决定了实例级标识符（如登录名和数据库名、变量、GOTO 标签和临时表的标识符）是否区分大小写。
实例级的排序规则在实例安装时指定。修改实例级的排序规则需要使用安装程序，非常麻烦。

4. 数据库级的排序规则还决定了数据库级标识符（如表、视图和列名）是否区分大小写，以及数据库中非Unicode字符串（如char/varchar/text）的字符代码页。
数据库级的排序规则通过 CREATE DATABASE 指定，通过 ALTER DATABASE 修改。

5. 数据库迁移或部署时，要确保源数据库和目标数据库的实例级/数据库级排序规则一致，否则很容易出现意想不到的问题。

6. 综上：
由于实例级和数据库级的排序规则牵涉众多，因而从经验来说，实例级、数据库级最好采用最常见、大家最熟悉的排序规则，并且在整个项目中尽量保持一致不变。
字段级排序规则偶尔会需要使用，但会影响到该字段的所有比较操作，慎重。
数据库编程中的特殊比较需求（如区分大小写、全半角等），通常通过表达式级排序规则来实现。
如果要处理不同的排序规则，则需要了解排序规则优先顺序。详见联机丛书。

[liouse]

喜欢这样的帖子a。

[liouse]

order by 有优先级的。

[清溪蓝郁]

[特别]

好贴要顶一下

[xiagirl]

学习。。。顶。。。

[cxmcxm]

好！！！！！！！！！！

[renwolin]

不错，先收下了。呵呵

[abuying]

很给力啊!

[x_q123456]

学习了。。。

[boxun123]

挺有用的

[panda2029]

恩，研究一下

[AustinLuo]

一个小技巧，没想到这么多人顶啊~

[蔬果达]

受教了 多谢

[AustinLuo]

结贴，差点都忘记了

[zhaogang1985]

不错、收下了

[mashengxuucom]

前面的该说的都说了,就不必再说了