Oracle 10g How to name the user, sort by initials, query

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 examples:

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:

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 finally the first letter of the Chinese character, can be used as a query condition

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

Finally, an example of combining the two together is shown:

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 ')

Transferred from: http://zheng12tian.iteye.com/blog/817571

Oracle 10g How to name the user, sort by initials, query