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

Source: Internet
Author: User

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:

  1. CREATE OR REPLACE FUNCTION f_trans_pinyin_capital (p_name in VARCHAR2) RETURN VARCHAR2 as
  2. V_compare VARCHAR2 (100);
  3. V_return VARCHAR2 (4000);
  4. FUNCTION F_nlssort (P_word in VARCHAR2) RETURN VARCHAR2 as
  5. BEGIN
  6. RETURN Nlssort (P_word, ' nls_sort=schinese_pinyin_m ');
  7. END;
  8. BEGIN
  9. For I in 1..LENGTH (p_name) LOOP
  10. V_compare: = F_nlssort (SUBSTR (P_name, I, 1));
  11. IF v_compare >= f_nlssort (' acridine ') and V_compare <= f_nlssort (' 驁 ') Then
  12. V_return: = V_return | |   ' a ';
  13. elsif v_compare >= f_nlssort (' eight ') and V_compare <= f_nlssort (' book ') Then
  14. V_return: = V_return | |   ' B ';
  15. elsif v_compare >= f_nlssort (' Cha ') and V_compare <= f_nlssort (' wrong ') Then
  16. V_return: = V_return | |   ' C ';
  17. elsif v_compare >= f_nlssort (' Otah ') and V_compare <= f_nlssort (' 鵽 ') Then
  18. V_return: = V_return | |   ' d ';
  19. elsif v_compare >= f_nlssort (' Ehegan ') and V_compare <= f_nlssort (' 樲 ') Then
  20. V_return: = V_return | |   ' E ';
  21. elsif v_compare >= f_nlssort (' FA ') and V_compare <= f_nlssort (' 猤 ') Then
  22. V_return: = V_return | |   ' F ';
  23. elsif v_compare >= f_nlssort (' Xu ') and V_compare <= f_nlssort (' hiker ') Then
  24. V_return: = V_return | |   ' G ';
  25. elsif v_compare >= f_nlssort (' 妎 ') and V_compare <= f_nlssort (' inceѕt ') Then
  26. V_return: = V_return | |   ' h ';
  27. elsif v_compare >= f_nlssort (' not ') and V_compare <= f_nlssort (' 攈 ') Then
  28. V_return: = V_return | |   ' J ';
  29. elsif v_compare >= f_nlssort (' ka ') and v_compare <= f_nlssort (' 穒 ') Then
  30. V_return: = V_return | |   ' K ';
  31. elsif v_compare >= f_nlssort (' junk ') and V_compare <= f_nlssort (' 擽 ') Then
  32. V_return: = V_return | |   ' l ';
  33. elsif v_compare >= f_nlssort (' 嘸 ') and V_compare <= f_nlssort (' 椧 ') Then
  34. V_return: = V_return | |   ' m ';
  35. elsif v_compare >= f_nlssort (' Hallasan ') and V_compare <= f_nlssort (' malarial ') Then
  36. V_return: = V_return | |   ' n ';
  37. elsif v_compare >= f_nlssort (' 筽 ') and V_compare <= f_nlssort (' 漚 ') Then
  38. V_return: = V_return | |   ' O ';
  39. elsif v_compare >= f_nlssort (' 妑 ') and V_compare <= f_nlssort (' exposure ') Then
  40. V_return: = V_return | |   ' P ';
  41. elsif v_compare >= f_nlssort (' seven ') and V_compare <= f_nlssort (' 裠 ') Then
  42. V_return: = V_return | |   ' Q ';
  43. elsif v_compare >= f_nlssort (' everybody ') and V_compare <= f_nlssort (' 鶸 ') Then
  44. V_return: = V_return | |   ' R ';
  45. elsif v_compare >= f_nlssort (' three ') and V_compare <= f_nlssort (' 蜶 ') Then
  46. V_return: = V_return | |   ' s ';
  47. elsif v_compare >= f_nlssort (' 侤 ') and V_compare <= f_nlssort (' 籜 ') Then
  48. V_return: = V_return | |   ' t ';
  49. elsif v_compare >= f_nlssort (' 屲 ') and V_compare <= f_nlssort (' clamoring ') Then
  50. V_return: = V_return | |   ' W ';
  51. elsif v_compare >= f_nlssort (' XI ') and V_compare <= f_nlssort (' 鑂 ') Then
  52. V_return: = V_return | |   ' x ';
  53. elsif v_compare >= f_nlssort (' ya ') and V_compare <= F_nlssort (' Wan Leng ') Then
  54. V_return: = V_return | |   ' Y ';
  55. elsif v_compare >= f_nlssort (' as ') and V_compare <= f_nlssort (' i ') Then
  56. V_return: = V_return | |   ' z ';
  57. END IF;
  58. END LOOP;
  59. RETURN V_return;
  60. 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:

  1. Select E.expert_id,e.eperson_id,e.fullname,
  2. SUM (b2i.browse_num) Browsecount,
  3. Count (o.object_id), Wmsys.wm_concat (distinct d.name)
  4. From expert E
  5. Left join Expert2disciplinetype e2d on e2d.expert_id = e.expert_id
  6. Left join Disciplinetype D on d.discipline_type_id = e2d.discipline_type_id
  7. and d.upid = ' 0 '
  8. Left join Community2expert c2e on c2e.expert_id = e.expert_id
  9. Left JOIN item I on i.item_type_id = ' academicres ' and i.withdrawn = ' N '
  10. Join Metadatavalue m on m.item_id = i.item_id and m.metadata_field_id = ' + '
  11. Join Objectpublishinfo o on o.object_id = i.item_id
  12. and O.object_type = ' item ' and o.viewobjecttype = ' Eperson '
  13. and O.viewobjectid = e.eperson_id and o.state = ' 1 '
  14. and o.publishstate_id = ' 3 '
  15. Left join Browse2item b2i on b2i.item_id = o.object_id
  16. where e.state = ' 1 ' and F_trans_pinyin_capital (substr (E.fullname,1,1)) = ' z '
  17. GROUP BY E.expert_id,e.eperson_id,e.fullname
  18. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.