《Oracle SQL疑难解析》——1.14 排序-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

《Oracle SQL疑难解析》——1.14 排序

简介:

本节书摘来自异步社区出版社《Oracle SQL疑难解析》一书中的第1章,第1.14节,作者: 【美】Grant Allen , Bob Bryla , Darl Kuhn,更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.14 排序

Oracle SQL疑难解析

1.14.1 要解决的问题

我们的文本数据混合了大写、小写字母和句子(英文句子,指第一个字母大写,后续字母小写)。我们希望把这些数据按字母顺序、大小写顺序进行排列。

1.14.2 解决方法

我们先在HR.EMPLOYEES表中创建一些混合大小写的数据。运行下面的UPDATE语句,把William Smith's的姓(last_name)改为大写:

update hr.employees
set last_name = 'SMITH'
where employee_id = 171;

下面的select语句要求按Oracle默认的排序方式显示根据last_name排序的结果:

select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
SMITH
Sarchand
Sciarra
Seo
Sewall
Smith
Stiles
Sullivan
…

聪明的读者可能已经猜到了排序结果。Oracle默认用二进制的排序方法。在上面这个简单的例子中,文本是按它们在代码页上对应数值的大小来排列的。编码可以是US7ASCII,WEIISO8859P1或其他编码方式。在这些代码页上,大、小写字母有不同的值,大写的值排在前面。这就是为什么上面查询结果中SMITH排在所有以“S”开头的姓的最前面。

人们通常希望看见两个“Smith”排在一起,忽略大小写。用这个NLS环境变量可以达到目的:

alter session set NLS_SORT='BINARY_CI';

select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
Sarchand
Sciarra
Seo
Sewall
Smith
SMITH
Stiles
Sullivan
…

1.14.3 数据库如何工作

Oracle支持区分大小写和不区分大小写两种排序方式。有个环境变量NLS_SORT进行区分大小写和不区分大小写的控制。默认的排序操作区分大小写,即NLS_SORT=BINARY。如果我们希望排序按照不区分大小写的方式,则需把环境变量NLS_SORT设置为BINARY_CI。

顾名思义,NLS_SORT仅仅影响排序结果,并不对其他大小写操作构成影响。即使把NLS_SORT设置为BINARY_CI,以不区分大小写的方式比较数据的操作仍然延续了Oracle默认的方式:

select first_name, last_name
from hr.employees
where last_name like 's%';
no rows selected

对以上结果别灰心。Oracle想到了你的需求,也提供了不区分大小写的比较方式。

比较传统的方式不是使用NLS环境变量,而是用UPPER和LOWER函数把要比较的字段名、文字都转成大写或小写后再比较。这种方法的不足之处在于,使用函数后,标准的索引就不能使用了,优化器无法工作,应对的方式是使用基于功能的索引(function-based index)。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章