转载:https://www.iteye.com/blog/hck-1494087
使用示例:
<select id="selectListOfCrowdFundingName" resultType="com.zgshfpw.grd_api.entity.po.CrowdFundingInfo">
select
(t.acr001 || '-' || t.acr002) as acr002,
t.acr001
from fc01 t, fc02 f
where t.acr001 = f.ACR001
and f.aaa110 = '2'
and t.acr011 = '0'
and t.acr018 in(4,5,6)
ORDER BY NLSSORT(acr002,'NLS_SORT = SCHINESE_PINYIN_M')
</select>
ALTER SESSION SET NLS_SORT=‘’; 排序影响整个会话
Oracle9i之前,中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
拼音 SELECT * FROM TEAM ORDER BY NLSSORT(列名,‘NLS_SORT = SCHINESE_PINYIN_M’)
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(列名,‘NLS_SORT = SCHINESE_STROKE_M’)
部首 SELECT * FROM TEAM ORDER BY NLSSORT(列名,‘NLS_SORT = SCHINESE_RADICAL_M’)
Oracle9i之前,中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
1、设置NLS_SORT参数值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
2、Session级别的设置,修改ORACLE字段的默认排序方式:
按拼音:alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画:alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁:alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M;
3、语句级别设置排序方式:
按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
4、修改系统参数(数据库所在操作系统):
set NLS_SORT=SCHINESE_RADICAL_M ;
export NLS_SORT (sh)
setenv NLS_SORT SCHINESE_RADICAL_M (csh)
HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)