ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle学习系列—关于字符数字混合排序和中文排序的问题

Oracle学习系列—关于字符数字混合排序和中文排序的问题

原创 Linux操作系统 作者:bq_wang 时间:2008-02-13 17:26:22 0 删除 编辑

关于汉字排序的问题源于曾经做过的114号码百事通查询,当时要求提供按笔画、部首、拼音的多种查询方式,拼音排序直接按名称字段进行排序时总有些是是而非的错误,百思不得其解;同时笔画、部首排序也摸不着头脑。。。

数字和英文混合排序

1,3,A,B,101,301,AB,BA

要求按照数字,字符分别进行排序,同时在统一类别数据中,首先按数字ASC,再次按长度排序,要求结果如下

1,101,3,301,A,AB,B,BA 或者

1,3,101,301,A,B,AB,BA

但是很奇怪,第一个排序似乎在合并两个视图之后,排序规则有点乱了。。。


CREATE TABLE TestNumberChar

(

SortName VARCHAR2(20)

)

INSERT INTO TestNumberChar VALUES('1');

INSERT INTO TestNumberChar VALUES('3');

INSERT INTO TestNumberChar VALUES('A');

INSERT INTO TestNumberChar VALUES('B');

INSERT INTO TestNumberChar VALUES('101');

INSERT INTO TestNumberChar VALUES('301');

INSERT INTO TestNumberChar VALUES('AB');

INSERT INTO TestNumberChar VALUES('BA');

COMMIT;

CREATE VIEW vSortNumber as

SELECT * FROM TestNumberChar

WHERE SUBSTR(SortName,1,1) IN ('0','1','2','3','4','5','6','7','8','9')

ORDER BY LENGTH(SortName),SortName

CREATE VIEW vSortChar as

SELECT * FROM TestNumberChar

WHERE SUBSTR(SortName,1,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')

ORDER BY LENGTH(SortName),SortName

SELECT * FROM vSortNumber

UNION

SELECT * FROM vSortChar

Order

Sort Result

1

1

2

101

3

3

4

301

5

A

6

AB

7

B

8

BA

SELECT * FROM TestNumberChar

ORDER BY TO_NUMBER

(

SUBSTR

(

SortName,1,LENGTH(SortName)-NVL

(

LENGTH

(

REPLACE

(

TRANSLATE(SortName,'0123456789.','00000000000'),'0'

)

),0

)

)

)

SELECT

SortName A,

TRANSLATE(SortName,'0123456789.','00000000000') B ,

REPLACE(TRANSLATE(SortName,'0123456789.','00000000000'),'0') C,

LENGTH(REPLACE(TRANSLATE(SortName,'0123456789.','00000000000'),

'0')) D,

NVL(LENGTH(REPLACE(TRANSLATE(SortName,'0123456789.',

'00000000000'),'0')),0) E,

LENGTH(SortName)-NVL(LENGTH(REPLACE(TRANSLATE(SortName,

'0123456789.','00000000000'),'0')),0) F,

SUBSTR(SortName,1,LENGTH(SortName)-

NVL(LENGTH(REPLACE(TRANSLATE(SortName,'0123456789.',

'00000000000'),'0')),0)) G,

TO_NUMBER(SUBSTR(SortName,1,

LENGTH(SortName)-NVL(LENGTH(REPLACE(

TRANSLATE(SortName,'0123456789.','00000000000'),'0')),0))) H

FROM TestNumberChar

ORDER BY TO_NUMBER(SUBSTR(SortName,1,

LENGTH(SortName)-NVL(LENGTH(REPLACE(TRANSLATE(SortName,

'0123456789.','00000000000'),'0')),0)))

Sort

A

B

C

D

E

F

G

H

1

1

0

0

1

1

1

2

3

0

0

1

3

3

3

101

000

0

3

101

101

4

301

000

0

3

301

301

5

A

A

A

1

1

0

6

B

B

B

1

1

0

7

BA

BA

BA

2

2

0

8

AB

AB

AB

2

2

0

关于汉字字符排序

CREATE TABLE TestHZ

(

SortName VARCHAR2(20)

)

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

INSERT INTO TestHZ VALUES ('');

COMMIT;

The NLSSORT Function

The NLSSORT function enables you to use any linguistic sort for an ORDER BY clause. It replaces a character string with the equivalent sort string used by the linguistic sort mechanism so that sorting the replacement strings produces the desired sorting sequence. For a binary sort, the sort string is the same as the input string.

The kind of linguistic sort used by an ORDER BY clause is determined by the NLS_SORT session parameter, but it can be overridden by explicitly using the NLSSORT function.

SELECT * FROM TestHZ

--按拼音排序
SELECT * FROM TestHZ

ORDER BY SortName

--按笔画(第一顺序)、部首(第二顺序)排序
SELECT * FROM TestHZ

ORDER BY NLSSORT(SortName,'NLS_SORT = SCHINESE_STROKE_M')
--
按部首(第一顺序)、拼音(第二顺序)排序

SELECT * FROM TestHZ

ORDER BY NLSSORT(SortName,'NLS_SORT = SCHINESE_RADICAL_M')
--
按拼音排序

SELECT * FROM TestHZ

ORDER BY NLSSORT(SortName,'NLS_SORT = SCHINESE_PINYIN_M')

粗略的看一下结果,Sort BySort By NLSSORT(XXX,’NLS_SORT = SCHINESE_PINYIN_M’)排序结果差不多,实际上,排序不同主要是针对一些比较生僻的汉字而言的,如“铑”。具体问题出在哪里,暂时也不清楚。。。

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1104346