Oracle alphabetical order with Chinese pinyin first letter function

Source: Internet
Author: User

Oracle alphabetical order with Chinese pinyin first letter function

According to the Chinese pinyin first alphabetical order, obtains the Chinese Pinyin first letter function
The first letter, stroke and radical sort function "Nlssort" are introduced according to Chinese characters:

1), First letter

SELECT * from T_table ORDER by Nlssort (NAME, ' nls_sort=schinese_pinyin_m ');

2), stroke
SELECT * from T_table ORDER by Nlssort (NAME, ' nls_sort=schinese_stroke_m ');

3), Radicals
SELECT * from T_table ORDER by Nlssort (NAME, ' nls_sort=schinese_radical_m ');

List A line of Chinese characters
With A as
(SELECT ' Get Chinese Pinyin first letter ' W from DUAL)
SELECT SUBSTR (W, ROWNUM, 1) from A
CONNECT by ROWNUM <= (SELECT LENGTH (W) from A);
return results
Been
Take
Chinese
Word
Spell
Sound
First
Word
Mother

Sort the list with the Chinese character set
With A as-www.2cto.com-
(SELECT ' Get Chinese Pinyin first letter ' W from DUAL)
SELECT SUBSTR (W, ROWNUM, 1) from A
CONNECT by ROWNUM <= (SELECT LENGTH (W) from A)
ORDER by Nlssort (SUBSTR (W, ROWNUM, 1), ' nls_sort=schinese_pinyin_m ');
return results
Chinese
Been
Mother
Spell
Take
First
Sound
Word
Word

So according to this principle, the above input a pinyin a beginning of the word "AO", followed by a B big head of the word "bar", to find the starting and ending of each syllable of the Chinese character is which
With A as
(
SELECT ROWNUM RN, CHR (ROWNUM) C from DUAL CONNECT by level <= 65535
)
SELECT * from A WHERE LENGTHB (C) = 2
and RN > 32768-www.2cto.com-
and Nlssort (C, ' Nls_sort=schinese_pinyin_m ') > Nlssort (' o ', ' nls_sort=schinese_pinyin_m ')
and Nlssort (C, ' Nls_sort=schinese_pinyin_m ') < Nlssort (' bar ', ' nls_sort=schinese_pinyin_m ')
ORDER by Nlssort (C, ' nls_sort=schinese_pinyin_m ');
According to the returned results, we can see that the end of a and the beginning of B are: "驁" "eight", and so on to find other points of demarcation, then the final function 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;

Test it:
SELECT f_trans_pinyin_capital (' Wang Seal ') from DUAL
Return First letter: WDF

Java enterprise-Class generic rights security framework source SPRINGMVC MyBatis or Hibernate+ehcache Shiro Druid Bootstrap HTML5

"Java Framework source code download"

Oracle alphabetical order with Chinese pinyin first letter function

Related Article

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.