Get a record in a table that starts with a phonetic alphabet

Today in the forum there is a post in a friend posted a good function, if the record has Chinese words, by using this function, you can easily identify which records are in a particular first letter pinyin.

Functions are 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
IF UPPER (SUBSTR (p_name,1,1)) >= ' A ' and UPPER (SUBSTR (p_name,1,1)) <= ' Z ' THEN
Return SUBSTR (p_name,1,1);
End IF;
V_compare: = F_nlssort (SUBSTR (p_name,1,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<= 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 (' 咑 ') and v_compare<= f_nlssort (' 鵽 ') THEN
V_return: = v_return| | ' D ';
elsif v_compare>= f_nlssort (' 妸 ') and v_compare<= f_nlssort (' 樲 ') THEN
V_return: = v_return| | ' E ';
elsif v_compare>= f_nlssort (' hair ') and v_compare<= f_nlssort (' 猤 ') THEN
V_return: = v_return| | ' F ';
elsif v_compare>= f_nlssort (' there ') and v_compare<= f_nlssort (' hiker ') THEN
V_return: = v_return| | ' G ';
elsif v_compare>= f_nlssort (' 妎 ') and v_compare<= f_nlssort (' 夻 ') THEN
V_return: = v_return| | ' H ';
elsif v_compare>= f_nlssort (' no ') 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 (' garbage ') 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 (' 拏 ') and v_compare<= f_nlssort (' malaria ') 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 (' VII ') 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 (' sa ') 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 (' 鶩 ') 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 (' rhythmic ') THEN
V_return: = v_return| | ' Y ';
elsif v_compare>= f_nlssort (' 帀 ') and v_compare<= f_nlssort (' Out ') THEN
V_return: = v_return| | ' Z ';
End IF;

return v_return;
End;
/

Later as long as
SELECT * FROM table

where f_trans_pinyin_capital (name) = ' A ' ORDER by Nlssort (name, ' nls_sort = Schinese_pinyin_m ')
You can find all the names that start with a and are sorted by pinyin.

Let's take an example to see

Sql> select * from T3;

NAME
----------------------------------------
An
Be
Hey

sql> CREATE OR REPLACE FUNCTION f_trans_pinyin_capital (
2 P_name in VARCHAR2
3) return VARCHAR2 as
4 v_compare VARCHAR2 (100);
5 V_return VARCHAR2 (4000);
6 FUNCTION F_nlssort (
7 P_word in VARCHAR2
8) return VARCHAR2 as
9 BEGIN
Ten return Nlssort (P_word, ' nls_sort=schinese_pinyin_m ');
one end;
BEGIN
IF UPPER (SUBSTR (p_name,1,1)) >= ' A ' and UPPER (SUBSTR (p_name,1,1)) <= ' Z ' TH
En
Return SUBSTR (p_name,1,1);
End IF;
V_compare: = F_nlssort (SUBSTR (p_name,1,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<= 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 (' 咑 ') and v_compare<= f_nlssort (' 鵽 ') THEN
V_return: = v_return| | ' D ';
elsif v_compare>= f_nlssort (' 妸 ') and v_compare<= f_nlssort (' 樲 ') THEN
num 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 (' there ') and v_compare<= f_nlssort (' hiker ') THEN
V_return: = v_return| | ' G ';
elsif v_compare>= f_nlssort (' 妎 ') and v_compare<= f_nlssort (' 夻 ') THEN
V_return: = v_return| | ' H ';
elsif v_compare>= f_nlssort (' no ') 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 ';
Notoginseng elsif v_compare>= f_nlssort (' garbage ') 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 (' 拏 ') and v_compare<= f_nlssort (' malaria ') 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_nl