在 Oracle 中漢字的排序

在9i 之前,對於中文oracle是用二進制進行排序的(binary)

在9i中新增了拼音,部首,筆劃排序.

SCHINESE_RADICAL_M 按照部首(第一順序)、筆劃(第二順序) 排序

SCHINESE_STROKE_M 按照筆劃(第一順序)、部首(第二順序)排序

SCHINESE_PINYIN_M 按照拼音排序[@more@]

-- 當前session的排序方式
SYS@HUIYI>select * from nls_session_parameters where parameter like 'NLS_SORT%';

PARAMETER  VALUE
---------- ------------------------------
NLS_SORT   BINARY

SYS@HUIYI>select gen01,gen02,dump(gen02) gen022 from gen order by gen02;

GEN01      GEN02      GEN022
---------- ---------- ----------------------------------------
S0405026   丁仙子     Typ=1 Len=6: 164,66,165,80,164,108
S0603005   占四鳳     Typ=1 Len=6: 165,101,165,124,187,241
S0603061   何新華     Typ=1 Len=6: 166,243,183,115,181,216
S0503034   周興宇     Typ=1 Len=6: 169,80,191,179,166,116
S0504027   張耀耀     Typ=1 Len=6: 177,105,196,163,196,163
S0501003   陶娟       Typ=1 Len=4: 179,179,174,83
S0201016   曾振能     Typ=1 Len=6: 180,191,174,182,175,224
S0505036   楊曉麗     Typ=1 Len=6: 183,168,190,229,196,82
S0504028   趙飛       Typ=1 Len=4: 187,175,173,184
S0508006   趙學艷     Typ=1 Len=6: 187,175,190,199,198,65

10 rows selected.

-- 修改排序方式
SYS@HUIYI>alter session set nls_sort='schinese_pinyin_m';

Session altered.

SYS@HUIYI>select gen01,gen02,dump(gen02) gen022 from gen order by gen02;

GEN01      GEN02      GEN022
---------- ---------- ----------------------------------------
S0405026   丁仙子     Typ=1 Len=6: 164,66,165,80,164,108
S0603061   何新華     Typ=1 Len=6: 166,243,183,115,181,216
S0501003   陶娟       Typ=1 Len=4: 179,179,174,83
S0505036   楊曉麗     Typ=1 Len=6: 183,168,190,229,196,82
S0201016   曾振能     Typ=1 Len=6: 180,191,174,182,175,224
S0603005   占四鳳     Typ=1 Len=6: 165,101,165,124,187,241
S0504027   張耀耀     Typ=1 Len=6: 177,105,196,163,196,163
S0504028   趙飛       Typ=1 Len=4: 187,175,173,184
S0508006   趙學艷     Typ=1 Len=6: 187,175,190,199,198,65
S0503034   周興宇     Typ=1 Len=6: 169,80,191,179,166,116

10 rows selected.

-- 在查詢中指定
SYS@HUIYI>select gen01,gen02,dump(gen02) gen022
  2   from gen
  3  order by nlssort(gen02,'nls_sort=binary');

GEN01      GEN02      GEN022
---------- ---------- ----------------------------------------
S0405026   丁仙子     Typ=1 Len=6: 164,66,165,80,164,108
S0603005   占四鳳     Typ=1 Len=6: 165,101,165,124,187,241
S0603061   何新華     Typ=1 Len=6: 166,243,183,115,181,216
S0503034   周興宇     Typ=1 Len=6: 169,80,191,179,166,116
S0504027   張耀耀     Typ=1 Len=6: 177,105,196,163,196,163
S0501003   陶娟       Typ=1 Len=4: 179,179,174,83
S0201016   曾振能     Typ=1 Len=6: 180,191,174,182,175,224
S0505036   楊曉麗     Typ=1 Len=6: 183,168,190,229,196,82
S0504028   趙飛       Typ=1 Len=4: 187,175,173,184
S0508006   趙學艷     Typ=1 Len=6: 187,175,190,199,198,65

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8799875/viewspace-903143/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8799875/viewspace-903143/