Oracle function to get Chinese pinyin first letter __ function

Oracle function gets Chinese Pinyin first letter
/*------Increase: yl--*/
Use of Nlssort functions in Oracle

Nlssort (), used for language sorting

Pinyin
SELECT * from Team orders by Nlssort (sort field name, ' Nls_sort = Schinese_pinyin_m ')
Strokes
SELECT * from Team orders by Nlssort (sort field name, ' Nls_sort = Schinese_stroke_m ')
Radical
SELECT * from Team orders by Nlssort (sort field name, ' Nls_sort = Schinese_radical_m ')
/*------Increase: YL end--*/

List one 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 Chinese character set
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)
Order BY Nlssort (SUBSTR (W, rownum, 1), ' nls_sort=schinese_pinyin_m ');
return results
Chinese
Been
Mother
Spell
Take
First
Sound
Word
Word

Then according to this principle, the above input a word "o" in Pinyin A, followed by a B big head of the word "bar", find the beginning and end 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
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 results of the return, we can see that the end of A and b of the men are: "驁" "eight", and so on to find other points of demarcation, then the last 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 (' VIII ') and V_compare <= (' book ') F_nlssort
V_return: = V_return | | ' B ';
elsif v_compare >= f_nlssort (' Cha ') and V_compare <= (' wrong ') f_nlssort
V_return: = V_return | | ' C ';
elsif v_compare >= f_nlssort (' 咑 ') and V_compare <= (' F_nlssort ') 鵽
V_return: = V_return | | ' d ';
elsif v_compare >= f_nlssort (' 妸 ') and V_compare <= (' F_nlssort ') 樲
V_return: = V_return | | ' E ';
elsif v_compare >= f_nlssort (' FA ') and V_compare <= (' F_nlssort ') 猤
V_return: = V_return | | ' F ';
elsif v_compare >= f_nlssort (' there ') and V_compare <= (' F_nlssort ') hiker
V_return: = V_return | | ' G ';
elsif v_compare >= f_nlssort (' 妎 ') and V_compare <= (' F_nlssort ') 夻
V_return: = V_return | | ' H ';
elsif v_compare >= f_nlssort (' no ') and V_compare <= (' F_nlssort ') 攈
V_return: = V_return | | ' J ';
elsif v_compare >= f_nlssort (' ka ') and V_compare <= (' F_nlssort ') 穒
V_return: = V_return | | ' K ';
elsif v_compare >= f_nlssort (' garbage ') and V_compare <= (' F_nlssort ') 擽
V_return: = V_return | | ' L ';
elsif v_compare >= f_nlssort (' 嘸 ') and V_compare <= (' F_nlssort ') 椧
V_return: = V_return | | ' m ';
elsif v_compare >= f_nlssort (' 拏 ') and V_compare <= (' f_nlssort ') malaria
V_return: = V_return | | ' N ';
elsif v_compare >= f_nlssort (' 筽 ') and V_compare <= (' F_nlssort ') 漚
V_return: = V_return | | ' O ';
elsif v_compare >= f_nlssort (' 妑 ') and V_compare <= (' exposure ') f_nlssort
V_return: = V_return | | ' P ';
elsif v_compare >= f_nlssort (' VII ') and V_compare <= (' F_nlssort ') 裠
V_return: = V_return | | ' Q ';
elsif v_compare >= f_nlssort (' Everybody ') and V_compare <= (' F_nlssort ') 鶸
V_return: = V_return | | ' R ';
elsif v_compare >= f_nlssort (' sa ') and v_compare <= (' F_nlssort ') 蜶
V_return: = V_return | | ' s ';
elsif v_compare >= f_nlssort (' 侤 ') and V_compare <= (' F_nlssort ') 籜
V_return: = V_return | | ' t ';
elsif v_compare >= f_nlssort (' 屲 ') and V_compare <= (' F_nlssort ') 鶩
V_return: = V_return | | ' W ';
elsif v_compare >= F_nlssort (' XI ') and V_compare <= (' F_nlssort ') 鑂
V_return: = V_return | | ' X ';
elsif v_compare >= f_nlssort (' ya ') and V_compare <= (' f_nlssort ') rhythmic
V_return: = V_return | | ' Y ';
elsif v_compare >= f_nlssort (' 帀 ') and V_compare <= (' Out ') f_nlssort
V_return: = V_return | | ' Z ';
End IF;
End LOOP;
return v_return;
End;

Test:
SELECT f_trans_pinyin_capital (' MabThera ') from DUAL
Back to LH
When using this query:
Select name from users t1 where (T1.name =? or F_trans_pinyin_capital (t1.name) =?)
If the inside of the parameter passed is "MabThera", is the query user name is a fallen flower, if the delivery is LH, the query is the pinyin is LH people