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:
- Pinyin
- SELECT * from TEAM ORDER by Nlssort (sort field,' nls_sort = Schinese_pinyin_m ')
- Strokes
- SELECT * from TEAM ORDER by Nlssort (sort field,' nls_sort = Schinese_stroke_m ')
- Radical
- 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:
- CREATE OR REPLACE FUNCTION f_trans_pinyin_capital (p_name in VARCHAR2) RETURN VARCHAR2 as
- V_compare VARCHAR2 (100);
- V_return VARCHAR2 (4000);
- FUNCTION F_nlssort (P_word in VARCHAR2) RETURN VARCHAR2 as
- BEGIN
- RETURN Nlssort (P_word, ' nls_sort=schinese_pinyin_m ');
- END;
- BEGIN
- For I in 1..LENGTH (p_name) LOOP
- V_compare: = F_nlssort (SUBSTR (P_name, I, 1));
- IF v_compare >= f_nlssort (' acridine ') and V_compare <= f_nlssort (' 驁 ') Then
- V_return: = V_return | | ' a ';
- elsif v_compare >= f_nlssort (' eight ') and V_compare <= f_nlssort (' book ') Then
- V_return: = V_return | | ' B ';
- elsif v_compare >= f_nlssort (' Cha ') and V_compare <= f_nlssort (' wrong ') Then
- V_return: = V_return | | ' C ';
- elsif v_compare >= f_nlssort (' Otah ') and V_compare <= f_nlssort (' 鵽 ') Then
- V_return: = V_return | | ' d ';
- elsif v_compare >= f_nlssort (' Ehegan ') and V_compare <= f_nlssort (' 樲 ') Then
- V_return: = V_return | | ' E ';
- elsif v_compare >= f_nlssort (' FA ') and V_compare <= f_nlssort (' 猤 ') Then
- V_return: = V_return | | ' F ';
- elsif v_compare >= f_nlssort (' Xu ') and V_compare <= f_nlssort (' hiker ') Then
- V_return: = V_return | | ' G ';
- elsif v_compare >= f_nlssort (' 妎 ') and V_compare <= f_nlssort (' inceѕt ') Then
- V_return: = V_return | | ' h ';
- elsif v_compare >= f_nlssort (' not ') and V_compare <= f_nlssort (' 攈 ') Then
- V_return: = V_return | | ' J ';
- elsif v_compare >= f_nlssort (' ka ') and v_compare <= f_nlssort (' 穒 ') Then
- V_return: = V_return | | ' K ';
- elsif v_compare >= f_nlssort (' junk ') and V_compare <= f_nlssort (' 擽 ') Then
- V_return: = V_return | | ' l ';
- elsif v_compare >= f_nlssort (' 嘸 ') and V_compare <= f_nlssort (' 椧 ') Then
- V_return: = V_return | | ' m ';
- elsif v_compare >= f_nlssort (' Hallasan ') and V_compare <= f_nlssort (' malarial ') Then
- V_return: = V_return | | ' n ';
- elsif v_compare >= f_nlssort (' 筽 ') and V_compare <= f_nlssort (' 漚 ') Then
- V_return: = V_return | | ' O ';
- elsif v_compare >= f_nlssort (' 妑 ') and V_compare <= f_nlssort (' exposure ') Then
- V_return: = V_return | | ' P ';
- elsif v_compare >= f_nlssort (' seven ') and V_compare <= f_nlssort (' 裠 ') Then
- V_return: = V_return | | ' Q ';
- elsif v_compare >= f_nlssort (' everybody ') and V_compare <= f_nlssort (' 鶸 ') Then
- V_return: = V_return | | ' R ';
- elsif v_compare >= f_nlssort (' three ') and V_compare <= f_nlssort (' 蜶 ') Then
- V_return: = V_return | | ' s ';
- elsif v_compare >= f_nlssort (' 侤 ') and V_compare <= f_nlssort (' 籜 ') Then
- V_return: = V_return | | ' t ';
- elsif v_compare >= f_nlssort (' 屲 ') and V_compare <= f_nlssort (' clamoring ') Then
- V_return: = V_return | | ' W ';
- elsif v_compare >= f_nlssort (' XI ') and V_compare <= f_nlssort (' 鑂 ') Then
- V_return: = V_return | | ' x ';
- elsif v_compare >= f_nlssort (' ya ') and V_compare <= F_nlssort (' Wan Leng ') Then
- V_return: = V_return | | ' Y ';
- elsif v_compare >= f_nlssort (' as ') and V_compare <= f_nlssort (' i ') Then
- V_return: = V_return | | ' z ';
- END IF;
- END LOOP;
- RETURN V_return;
- 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
- 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:
- Select E.expert_id,e.eperson_id,e.fullname,
- SUM (b2i.browse_num) Browsecount,
- Count (o.object_id), Wmsys.wm_concat (distinct d.name)
- From expert E
- Left join Expert2disciplinetype e2d on e2d.expert_id = e.expert_id
- Left join Disciplinetype D on d.discipline_type_id = e2d.discipline_type_id
- and d.upid = ' 0 '
- Left join Community2expert c2e on c2e.expert_id = e.expert_id
- Left JOIN item I on i.item_type_id = ' academicres ' and i.withdrawn = ' N '
- Join Metadatavalue m on m.item_id = i.item_id and m.metadata_field_id = ' + '
- Join Objectpublishinfo o on o.object_id = i.item_id
- and O.object_type = ' item ' and o.viewobjecttype = ' Eperson '
- and O.viewobjectid = e.eperson_id and o.state = ' 1 '
- and o.publishstate_id = ' 3 '
- Left join Browse2item b2i on b2i.item_id = o.object_id
- where e.state = ' 1 ' and F_trans_pinyin_capital (substr (E.fullname,1,1)) = ' z '
- GROUP BY E.expert_id,e.eperson_id,e.fullname
- 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