Use ORACLE functions to query by the first letter of Chinese characters and Pinyin

Using ORACLE functions can implement many of the functions we need. The following describes how to use ORACLE functions to query by the first letter of Chinese characters and pinyin. If you are interested in using ORACLE functions, take a look.

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 ORACLE 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 ('longhua ') FROM DUAL
Return lh
The query is as follows:
Select name from users t1 where (t1.name =? Or F_TRANS_PINYIN_CAPITAL (t1.name) = ?)
If? The parameter is passed by "Luo Hua", that is, the user name of the query is the person who spent the query. If the user name is lh, the query is the person whose Pinyin is lh.
 

Implementation of oracle function return table

Learn about the Oracle FBI Index

How to install Oracle as a Linux Service

Multiple table Connection Methods in Oracle

Comparison Between Common DB2 functions and Oracle Functions