How to sort Chinese in Oracle

Test database version of Chinese sorted:

Sql> select * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition release 11.1.0.6.0-production

Pl/sql Release 11.1.0.6.0-production

CORE 11.1.0.6.0 Production

TNS for Linux:version 11.1.0.6.0-production

Nlsrtl Version 11.1.0.6.0-production

2. Create a test table

CREATE TABLE player (ID number (0), name VARCHAR2 (30));

3. Check Character Set configuration

Confirm the database character set American_america. Al32utf8

Sql> select name,value$ from props$;

NAME value$

------------------------------ ------------------------------

DICT. BASE 2

Default_temp_tablespace TEMP

Default_permanent_tablespace USERS

Default_edition Ora$base

Flashback Timestamp TimeZone GMT

tde_master_key_id

Default_tbs_type Smallfile

Nls_language American

Nls_territory AMERICA

Nls_currency $

Nls_iso_currency AMERICA

Nls_numeric_characters.,

Nls_characterset Al32utf8

Nls_calendar Gregorian

Nls_date_format DD-MON-RR

Nls_date_language American

Nls_sort BINARY

Nls_time_format HH.MI. Ssxff AM

Nls_timestamp_format DDMON-RR HH.MI. Ssxff AM

Nls_time_tz_format HH.MI. Ssxff AM TZR

Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR

Nls_dual_currency $

Nls_comp BINARY

Nls_length_semantics BYTE

NLS_NCHAR_CONV_EXCP FALSE

Nls_nchar_characterset AL16UTF16

Nls_rdbms_version 11.1.0.6.0

Global_db_name ora11g

Export_views_version 8

Workload_capture_mode

Workload_replay_mode

Dbtimezone 00:00

Rows selected.

Sql>

Deeply confirm character Set

SELECT DISTINCT (Nls_charset_name (Charsetid)) CHARACTERSET,

DECODE (type#, 1,

DECODE (Charsetform, 1, ' VARCHAR2 ', 2, ' NVARCHAR2 ', ' unkown '),

9,

DECODE (Charsetform, 1, ' VARCHAR ', 2, ' NCHAR varying ', ' unkown '),

96,

DECODE (Charsetform, 1, ' CHAR ', 2, ' NCHAR ', ' unkown '),

112,

DECODE (Charsetform, 1, ' CLOB ', 2, ' NCLOB ', ' unkown ')) types_used_in

From SYS. col$

WHERE Charsetform in (1, 2)

and type# in (1, 9, 96, 112);

Confirm operating system Character set settings

[Ora11g@bj55 ~]$ Export Nls_lang=american_america.al32utf8

4. Fill in the test data

。。。。

Sql> INSERT into player values (17, ' PM ');

1 row created.

Sql> INSERT into player values (20, ' 8-kilometer death ');

Sql> Set pagesize 60

Sql> select * from player;

ID NAME

　　

　　

1 Chinese

2 Chinese Pinyin

3 Strokes Input Method

41

52

63

74

85

96

107

1281

1382

1491

150

16 Tests.

17 afternoon.

208-kilometer death

Rows selected.

Sql>

5. Start the test, found that three query statements return the same results

Sql> SELECT * from Player ORDER by Nlssort (name, ' Nls_sort=schinese_radical_m ');

ID NAME

---------- --------------------

41

150

74

107

96

85

63

3 Strokes Input Method

1382

1491

1281

52

1 Chinese

17 afternoon.

2 Chinese Pinyin

16 Tests.

208-kilometer death

Rows selected.

Sql> SELECT * from Player ORDER by Nlssort (name, ' Nls_sort=schinese_stroke_m ');

ID NAME

---------- --------------------

41

150

74

107

96

85

63

3 Strokes Input Method

1382

1491

1281

52

1 Chinese

17 afternoon.

2 Chinese Pinyin

16 Tests.

208-kilometer death

Rows selected.

Sql> SELECT * from Player ORDER by Nlssort (name, ' Nls_sort=schinese_pinyin_m ');

ID NAME

---------- --------------------------------

41

150

74

107

96

85

63

3 Strokes Input Method

1382

1491

1281

52

1 Chinese

17 afternoon.

2 Chinese Pinyin

16 Tests.

208-kilometer death

Rows selected.