About implementing a solution for sorting Chinese initials in the Oracle database in Java

First of all, the Oracle 9i added a new system comes with the sorting function
1. Sort by first letter
New in oracle9i, according to pinyin, radicals, stroke sorting function. Setting the Nls_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
New in oracle9i by pinyin, radicals, stroke sorting function
Usage Example: Java code

1. Pinyin
2. SELECT * from TEAM ORDER by Nlssort (sort field,' nls_sort = Schinese_pinyin_m ')
3. Strokes
4. SELECT * from TEAM ORDER by Nlssort (sort field,' nls_sort = Schinese_stroke_m ')
5. Radical
6. SELECT * from TEAM ORDER by Nlssort (sort field,' nls_sort = Schinese_radical_m ')

2, according to the first letter pinyin query
First we want to create a function that can query the first letter of Chinese characters, the function code is as follows: Java code

2. CREATE OR REPLACE FUNCTION f_trans_pinyin_capital (p_name in VARCHAR2) RETURN VARCHAR2 as
4. V_compare VARCHAR2 (100);
6. V_return VARCHAR2 (4000);
10. FUNCTION F_nlssort (P_word in VARCHAR2) RETURN VARCHAR2 as
12. BEGIN
14. RETURN Nlssort (P_word, ' nls_sort=schinese_pinyin_m ');
16. END;
18. BEGIN
20. For I in 1..LENGTH (p_name) LOOP
22. V_compare: = F_nlssort (SUBSTR (P_name, I, 1));
24. IF v_compare >= f_nlssort (' acridine ') and V_compare <= f_nlssort (' 驁 ') Then
26. V_return: = V_return | |   ' a ';
28. elsif v_compare >= f_nlssort (' eight ') and V_compare <= f_nlssort (' book ') Then
30. V_return: = V_return | |   ' B ';
32. elsif v_compare >= f_nlssort (' Cha ') and V_compare <= f_nlssort (' wrong ') Then
34. V_return: = V_return | |   ' C ';
36. elsif v_compare >= f_nlssort (' Otah ') and V_compare <= f_nlssort (' 鵽 ') Then
38. V_return: = V_return | |   ' d ';
40. elsif v_compare >= f_nlssort (' Ehegan ') and V_compare <= f_nlssort (' 樲 ') Then
42. V_return: = V_return | |   ' E ';
44. elsif v_compare >= f_nlssort (' FA ') and V_compare <= f_nlssort (' 猤 ') Then
46. V_return: = V_return | |   ' F ';
48. elsif v_compare >= f_nlssort (' Xu ') and V_compare <= f_nlssort (' hiker ') Then
50. V_return: = V_return | |   ' G ';
52. elsif v_compare >= f_nlssort (' 妎 ') and V_compare <= f_nlssort (' inceѕt ') Then
54. V_return: = V_return | |   ' h ';
56. elsif v_compare >= f_nlssort (' not ') and V_compare <= f_nlssort (' 攈 ') Then
58. V_return: = V_return | |   ' J ';
60. elsif v_compare >= f_nlssort (' ka ') and v_compare <= f_nlssort (' 穒 ') Then
62. V_return: = V_return | |   ' K ';
64. elsif v_compare >= f_nlssort (' junk ') and V_compare <= f_nlssort (' 擽 ') Then
66. V_return: = V_return | |   ' l ';
68. elsif v_compare >= f_nlssort (' 嘸 ') and V_compare <= f_nlssort (' 椧 ') Then
70. V_return: = V_return | |   ' m ';
72. elsif v_compare >= f_nlssort (' Hallasan ') and V_compare <= f_nlssort (' malarial ') Then
74. V_return: = V_return | |   ' n ';
76. elsif v_compare >= f_nlssort (' 筽 ') and V_compare <= f_nlssort (' 漚 ') Then
78. V_return: = V_return | |   ' O ';
80. elsif v_compare >= f_nlssort (' 妑 ') and V_compare <= f_nlssort (' exposure ') Then
82. V_return: = V_return | |   ' P ';
84. elsif v_compare >= f_nlssort (' seven ') and V_compare <= f_nlssort (' 裠 ') Then
86. V_return: = V_return | |   ' Q ';
88. elsif v_compare >= f_nlssort (' everybody ') and V_compare <= f_nlssort (' 鶸 ') Then
90. V_return: = V_return | |   ' R ';
92. elsif v_compare >= f_nlssort (' three ') and V_compare <= f_nlssort (' 蜶 ') Then
94. V_return: = V_return | |   ' s ';
96. elsif v_compare >= f_nlssort (' 侤 ') and V_compare <= f_nlssort (' 籜 ') Then
98. V_return: = V_return | |   ' t ';
100. elsif v_compare >= f_nlssort (' 屲 ') and V_compare <= f_nlssort (' clamoring ') Then
102. V_return: = V_return | |   ' W ';
104. elsif v_compare >= f_nlssort (' XI ') and V_compare <= f_nlssort (' 鑂 ') Then
106. V_return: = V_return | |   ' x ';
108. elsif v_compare >= f_nlssort (' ya ') and V_compare <= F_nlssort (' Wan Leng ') Then
110. V_return: = V_return | |   ' Y ';
112. elsif v_compare >= f_nlssort (' as ') and V_compare <= f_nlssort (' i ') Then
114. V_return: = V_return | |   ' z ';
116. END IF;
118. END LOOP;
120. RETURN V_return;
122. END;

Then, we can take the name first, then intercept the first character, and then the first letter of the Chinese character, can be used as the query condition Java code

1. Select e.fullname,e.expert_id from Expert e where f_trans_pinyin_capital (substr (E.fullname,1,1)) = ' Z '

Finally, we show an example of combining the two: Java code

2. Select E.expert_id,e.eperson_id,e.fullname,
4. SUM (b2i.browse_num) Browsecount,
6. Count (o.object_id), Wmsys.wm_concat (distinct d.name)
8. From expert E
10. Left join Expert2disciplinetype e2d on e2d.expert_id = e.expert_id
12. Left join Disciplinetype D on d.discipline_type_id = e2d.discipline_type_id
14. and d.upid = ' 0 '
16. Left join Community2expert c2e on c2e.expert_id = e.expert_id
18. Left JOIN item I on i.item_type_id = ' academicres ' and i.withdrawn = ' N '
20. Join Metadatavalue m on m.item_id = i.item_id and m.metadata_field_id = ' + '
22. Join Objectpublishinfo o on o.object_id = i.item_id
24. and O.object_type = ' item ' and o.viewobjecttype = ' Eperson '
26. and O.viewobjectid = e.eperson_id and o.state = ' 1 '
28. and o.publishstate_id = ' 3 '
30. Left join Browse2item b2i on b2i.item_id = o.object_id
32. where e.state = ' 1 ' and F_trans_pinyin_capital (substr (E.fullname,1,1)) = ' z '
34. GROUP BY E.expert_id,e.eperson_id,e.fullname
36. Order by Nlssort (E.fullname,' nls_sort=schinese_pinyin_m ')

About implementing a solution for sorting Chinese initials in the Oracle database in Java