Summary of order by sorting of Chinese characters and Chinese fields in Oracle Database

I. The most common sorting method

1) sort by strokes

Select * from Table order by nlssort (columnName, 'nls _ SORT = SCHINESE_STROKE_M ')

2) sort by department heads

Select * from Table order by nlssort (columnName, 'nls _ SORT = SCHINESE_RADICAL_M ')

3) sort by pinyin

Select * from Table order by nlssort (columnName, 'nls _ SORT = SCHINESE_PINYIN_M ');

Supplement:

1. Sorting in Oracle official documents is described as follows:

In the Table "Table A-15 Multilingual LInguistic Sorts", let's take a look at the content about the Chinese sorting (the first three are related to the simplified Chinese sorting, and the last two are related to the traditional Chinese sorting ):

1) SCHINESE_RADICAL_M
Simplified Chinese sort based on radical as primary order and number of strokes order as secondary order
Note: in simplified Chinese, the order is "beginning", and the order is "number of strokes;

2) SCHINESE_STROKE_M
Simplified Chinese sort uses number of strokes as primary order and radical as secondary order
Note: in simplified Chinese, the "number of strokes" in the first order is the "beginning" in the second order;

3) SCHINESE_PINYIN_M
Simplified Chinese PinYin sorting order
Note: Simplified Chinese are sorted by pinyin;

4) TCHINESE_RADICAL_M
Traditional Chinese sort based on radical as primary order and number of strokes order as secondary order
Note: In traditional Chinese, the first order is "beginning", and the second order is "number of strokes;

5) TCHINESE_STROKE_M
Traditional Chinese sort uses number of strokes as primary order and radical as secondary order. It supports supplementary characters.
Note: In traditional Chinese, the "number of strokes" in the first order is the "beginning" in the second order;

2. Create an experiment table T and initialize six records.
Sec @ secooler> create table t (x varchar2 (10 ));
Sec @ secooler> insert into t values ('Hou ');
Sec @ secooler> insert into t values ('you ');
Sec @ secooler> insert into t values ('do ');
Sec @ secooler> insert into t values ('late ');
Sec @ secooler> insert into t values ('push ');
Sec @ secooler> insert into t values ('detaching ');
Sec @ secooler> commit;
Sec @ secooler> select * from t;

X
------------------------------
Hou
You
Do
Pull
Push
Split

6 rows selected.

3. Confirm the database version and character set
Sec @ secooler> select * from v $ version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

Sec @ secooler> select userenv ('language') from dual;

USERENV ('language ')
-----------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

4. In this environment, view the default Chinese character sorting rules
Sec @ secooler> select * from t order by x;

X
------------------------------
Split
Hou
Pull
You
Push
Do

6 rows selected.

The default Chinese character sorting rule is "pinyin ".

5. Use nlssort to force sorting by pinyin
Sec @ secooler> select * from t order by nlssort (x, 'nls _ SORT = SCHINESE_PINYIN_M ');

X
------------------------------
Split
Hou
Pull
You
Push
Do

6 rows selected.

6. Use nlssort to forcibly sort by "beginning" (first order) and "number of strokes" (second order)
Sec @ secooler> select * from t order by nlssort (x, 'nls _ SORT = SCHINESE_RADICAL_M ');

X
------------------------------
You
Hou
Do
Split
Pull
Push

6 rows selected.

The Chinese characters next to a single person are placed in front, while the Chinese characters next to a single person are placed in the back; the three Chinese characters next to a single person are further sorted by the number of strokes.

7. Use nlssort to forcibly sort by number of strokes (first order) and "beginning" (second order)
Sec @ secooler> select * from t order by nlssort (x, 'nls _ SORT = SCHINESE_STROKE_M ');

X
------------------------------
You
Split
Pull
Hou
Do
Push

6 rows selected.

It can be seen that the number of strokes in the future is more.