Oracle alphabetical order with Chinese pinyin first letter function

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