[DB] [Oracle] sorts the Chinese pinyin initials and obtains the Chinese pinyin initials.

Sorts and retrieves the first letter of Chinese pinyin according to the first letter of Chinese pinyin.

This section describes the function of ranking by Chinese characters including the first letter, strokes, and radicals. [nlssort ]:

1) First Letter

Select * From t_table order by nlssort (name, 'nls _ sort = schinese_pinyin_m ');

2) strokes
Select * From t_table order by nlssort (name, 'nls _ sort = schinese_stroke_m ');

3) department heads
Select * From t_table order by nlssort (name, 'nls _ sort = schinese_radical_m ');

List one line of Chinese Characters
With a
(Select 'get the first letter of Chinese pinyin 'W from dual)
Select substr (W, rownum, 1) from
Connect by rownum <= (select length (w) from );
Returned results
Obtain
Fetch
Han
Word
Fight
Audio
First
Word
Mother

Sort the list using the Chinese Character Set
With a
(Select 'get the first letter of Chinese pinyin 'W from dual)
Select substr (W, rownum, 1) from
Connect by rownum <= (select length (w) from)
Order by nlssort (substr (W, rownum, 1), 'nls _ sort = schinese_pinyin_m ');
Returned results
Han
Obtain
Mother
Fight
Fetch
First
Audio
Word
Word

Then, based on this principle, enter an Australian character prefixed by pinyin A, followed by a Chinese character typed by pinyin A, and find the starting and ending Chinese character of each syllable.
With a
(
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 ('ao', '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 ');
Based on the returned results, we can see that the end of a and the start of B are respectively "Eight", and other demarcation points can be found in turn, the final function is as follows:

Create or replace function f_trans_pinyin_capital (p_name in varchar2) return varchar2
V_compare varchar2 (100 );
V_return varchar2 (4000 );

Function f_nlssort (p_word in varchar2) return varchar2
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 ('tag') and v_compare <= f_nlssort ('tag') then
V_return: = v_return | 'a ';
Elsif v_compare> = f_nlssort ('8') and v_compare <= f_nlssort (' ') then
V_return: = v_return | 'B ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'C ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'D ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'E ';
Elsif v_compare> = f_nlssort ('fout') and v_compare <= f_nlssort ('hangzhou') then
V_return: = v_return | 'F ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'G ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'H ';
Elsif v_compare> = f_nlssort ('stop') and v_compare <= f_nlssort ('stopped') then
V_return: = v_return | 'J ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'K ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'l ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'M ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'n ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'O ';
Elsif v_compare> = f_nlssort ('taobao') and v_compare <= f_nlssort ('overview') then
V_return: = v_return | 'P ';
Elsif v_compare> = f_nlssort ('7') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'q ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'R ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return |'s ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'T ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'W ';
Elsif v_compare> = f_nlssort ('shanghai') and v_compare <= f_nlssort ('shanghai') then
V_return: = v_return | 'X ';
Elsif v_compare> = f_nlssort ('ya ') and v_compare <= f_nlssort ('y') then
V_return: = v_return | 'y ';
Elsif v_compare> = f_nlssort ('hour') and v_compare <= f_nlssort ('hour') then
V_return: = v_return | 'Z ';
End if;
End loop;
Return v_return;
End;

Test:
Select f_trans_pinyin_capital ('wang defeng') from dual

Return initial letter: WDF