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

原创 2011年01月21日 23:56:00

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

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

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

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

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

image

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

image

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 'Simith','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
Simith     13911111111
uonun      13812345678

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

区分大小写,不区分全半角
id          c.uname    i.uname    phone
----------- ---------- ---------- -----------
2           Simith     Simith     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

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

因为偶然的原因,需要在INNER JOIN联表时,让对应字段进行区分大小写的比较。而默认情况下建立的Sql Server数据库是不区分大小写的,这个需求怎么实现呢? 要实现这个需求,至少有三个操作级...
  • uonun
  • uonun
  • 2011年01月21日 23:56
  • 696

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

因为偶然的原因,需要在INNER JOIN联表时,让对应字段进行区分大小写的比较。而默认情况下建立的Sql Server数据库是不区分大小写的,这个需求怎么实现呢? 要实现这个需求,至少有三个操...
  • wdxgdiy
  • wdxgdiy
  • 2013年04月16日 12:59
  • 688

如何设置Sql Server数据库查询时区分大小写和全角半角

zhuanzai 开发中常用的数据库有Oracle和Sql Server,Oracle数据库在查询的时候是有大小写区分的,而Sql Server数据库则不区分大小写,如何使Sql Server数据库...
  • lingxyd_0
  • lingxyd_0
  • 2014年04月21日 10:35
  • 4207

SQLServer数据库区分大小写的几种方法

 刚刚使用sql server没有几天, 发现一个很郁闷的问题, sql server 竟然不区分大小写. 比如: select * from abc_tbl where field_1 = abc,...
  • WINCOL
  • WINCOL
  • 2007年05月15日 15:20
  • 10344

SQL SERVER不认全角和半角

 不管是c/s还是b/s的开发模式,随着window操作系统的不断深入人心,其日渐强大的数据库SQL SERVER也逐渐被大家所认可。 在一次偶然的错误处理中,发现居然全角字符和半角字符都能通过SQL...
  • htitzx
  • htitzx
  • 2005年05月25日 16:20
  • 1061

phpMyAdmin配置记录

windows下直接解压到网站目录就能用linux下解压到网站目录后需要修改一下配置文件,具体修改方式官网有详细的说明。按照官网的方式配完以后登录出现了这样的错误: phpmyadmin #200...
  • alan19931103
  • alan19931103
  • 2016年04月10日 16:51
  • 729

php mysql No such file or directory[2002]

可能是由于mysql.sock不在搜索路径里, 在如下目录建立mysql.sock的连接 /tmp/ /var/mysql/...
  • 0210
  • 0210
  • 2015年03月08日 17:45
  • 2119

Mysql如何在SQL中实现区分大小写的查询

1.问题描述  mysql版本:mariadb 10.1.16,采用utf8-ci,不区分大小写的排序规则。  开发人员反应业务要求要区分大小写,就是说当输入小写条件时,只能返回小写条件对应的数据...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年12月04日 22:37
  • 1112

关于SQL SERVER排序规则

 一、排序规则简介: 什么叫排序规则呢?MS是这样描述的:"在 Microsoft SQL Server  中,字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用...
  • feixianxxx
  • feixianxxx
  • 2009年10月05日 12:01
  • 25148

SQL Server 2008 中SQL查询语句中字段值不区分大小写问题处理

一、问题最近在一个系统的对外接口中,经过测试人员测试,发现登录接口中输入的用户名不区分大小写,经过排查找原因,原来是由于数据库忽略了大小写。二、解决办法Select *  from Base_User...
  • taomanman
  • taomanman
  • 2016年07月19日 12:03
  • 4660
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server数据库查询区分大小写、全半角——排序规则的应用
举报原因:
原因补充:

(最多只允许输入30个字)