Oracle Chinese sort NLSSORT

Today, I found an interesting function: NLSSORT. I found that I could sort Chinese by pinyin, strokes and radicals, so I checked the relevant data. The results are as follows:

Starting from Oracle 9i, the function of sorting by pinyin, radical and stroke has been added.

This is achieved by setting the NSL_ sort value:

SCHINESE_RADICAL_M sorts by radical (first order) and strokes (second order)

SCHINESE_STROKE_M sorts by stroke (first order) and radical (second order)

SCHINESE_PINYIN_M is sorted by pinyin

There are two common ways to implement Chinese sorting:

Session level

ALTER SESSION SET NLS_SORT='XXX'

This result affects the entire session.

Sql level

SELECT * FROM TABLE_XXX ORDER BY NLSSORT (field name, 'NLS_SORT=XXX')

Example:

CREATE TABLE test_sort (NAME VARCHAR2 (50)); INSERT INTO test_sort (NAME) VALUES ('China'); INSERT INTO test_sort (NAME) VALUES ('Shanxi'); INSERT INTO test_sort (NAME) VALUES ('Beijing'); INSERT INTO test_sort (NAME) VALUES ('Shaanxi'); INSERT INTO test_sort (NAME) VALUES ('Hebei'); INSERT INTO test_sort (NAME) VALUES ('Hubei'); INSERT INTO test_sort (NAME) VALUES ('Henan')

Default sort result: the default sort is BINARY, that is, binary sort

SQL > SELECT * FROM test_sort ORDER BY NAME;NAME--- China Beijing Shanxi Hebei Henan Hubei Shaanxi 7 rows selected

Pinyin sorting result:

SQL > SELECT * FROM test_sort ORDER BY NLSSORT (NAME, 'NLS_SORT=SCHINESE_PINYIN_M'); NAME--- Beijing, Hebei, Henan, Hubei, Shanxi, Shaanxi, China 7 rows selected

Stroke sort result:

SQL > SELECT * FROM test_sort ORDER BY NLSSORT (NAME, 'NLS_SORT=SCHINESE_STROKE_M'); NAME--- Shanxi, Beijing, China, Hebei, Henan, Shaanxi, Hubei 7 rows selected

Radical sort result:

SQL > SELECT * FROM test_sort ORDER BY NLSSORT (NAME, 'NLS_SORT=SCHINESE_RADICAL_M'); NAME--- Beijing, China, Shanxi, Hebei, Henan, Hubei, Shaanxi 7 rows selected