Chinese phonetic sorting problem in Oracle database

Oracle database Chinese phonetic sorting problem, nls_sort setting

Before Oracle9i, Chinese is sorted according to binary encoding. In oracle9i, according to pinyin, radical, stroke sorting function.

1, set the Nls_sort parameter value

Schinese_radical_m sorted by radical (first order), stroke (second order)

Schinese_stroke_m sorted by stroke (first order), radical (second order)

Schinese_pinyin_m sorted by Pinyin

Binary_ci ignores case-insensitive sorting

2, the session level settings, modify the Oracle field default sorting method:

by Pinyin: Alter session set nls_sort = Schinese_pinyin_m;

By stroke: Alter session set nls_sort = Schinese_stroke_m;

By radicals: Alter session set nls_sort = Schinese_radical_m;

Ignore case: Alter session set Nls_sort = binary_ci;

3, the statement level set sorting method:

Sort by stroke

SELECT * FROM Dept ORDER by Nlssort (name, ' Nls_sort=schinese_stroke_m ');

Sort by radical

SELECT * FROM Dept ORDER by Nlssort (name, ' Nls_sort=schinese_radical_m ');

Sorted by Pinyin, this is the system's default sorting method

SELECT * FROM Dept ORDER by Nlssort (name, ' Nls_sort=schinese_pinyin_m ');

Ignore case

SELECT * FROM Dept ORDER by Nlssort (name, ' nls_sort=binary_ci');

4. Modify system parameters (Database operating system):

Set Nls_sort=schinese_radical_m export Nls_sort (SH)

Setenv nls_sort schinese_radical_m (CSH)

Hklc\software\oracle\home0\nls_sort (Win registration form)

5, set the query to ignore the case method:

Alter session set nls_sort= 'binary_ci';

Alter session set nls_comp= 'linguistic';