Oracle Chinese sort nlssort nls_sort

Tag: Type field name--order span arch 16px set Chinese

Today found a very interesting function: Nlssort, found to Chinese by pinyin, strokes, radical sort, then looked up the relevant information, the results are as follows:

Oracle 9i begins with the addition of the pinyin, radical, stroke sorting feature.

This is done by setting the Nsl_sort value:

schinese_radical_m Sort by radical (first order), stroke (second order)

schinese_stroke_m Sort by stroke (first order), radical (second order)

schinese_pinyin_m sorting by pinyin

There are two common ways to achieve Chinese ordering:

1. Session level

   ALTER SESSION SET nls_sort= ' XXX ';

   This result affects the entire session.

2. SQL-level

   SELECT * from Table_xxx ORDER by Nlssort (field name, ' nls_sort=xxx ');

Cases:

CREATE TABLE test_sort (name VARCHAR2), 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: default sort in binary sort , binary sort

Sql> SELECT * from Test_sort ORDER by NAME; NAME--------------------------------------------------China Beijing Shanxi Hebei Henan Hubei Shaanxi 7 rows selected

Phonetic sorting results:

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 China Beijing 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--------------------------------------------------China Beijing Shanxi Hebei Henan Hubei Shaanxi 7 rows selected

Oracle Chinese sort nlssort nls_sort