ORACLE sorts the Chinese pinyin initials and obtains the Chinese pinyin initials.

Source: Internet
Author: User


Sort BY Chinese pinyin initials and obtain the Chinese pinyin initials. Introduction: sort BY Chinese Initials, strokes, and radicals. [NLSSORT]: 1) SELECT * FROM T_TABLE order by nlssort (NAME, 'nls _ SORT = SCHINESE_PINYIN_M '); 2) SELECT * FROM T_TABLE order by nlssort (NAME, 'nls _ SORT = SCHINESE_STROKE_M'); 3) SELECT * FROM T_TABLE order by nlssort (NAME, 'nls _ SORT = SCHINESE_RADICAL_M '); list A line of Chinese characters with a as (SELECT 'get the first letter of Chinese pinyin' w from dual) select substr (W, ROWNUM, 1) from a connect by rownum <= (select length (W) from ); obtain the first letter of the Chinese Character Set and sort the list with a as -www.2cto.com-(SELECT 'get the first letter of the Chinese character pinyin '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 '); if the returned result is A Chinese character, the Chinese character is spelled as the first phoneme. Then, based on this principle, enter A Chinese character "A" and A Chinese character "", find the starting and ending Chinese characters of each syllable with a as (select rownum rn, CHR (ROWNUM) c from dual connect by level <= 65535) SELECT * from a where lengthb (C) = 2AND RN> 32768 -www.2cto.com-and nlssort (C, 'nls _ SORT = SCHINESE_PINYIN_M ')> NLSSORT ('ao', 'nls _ SORT = done') and nlssort (C, 'nls _ SORT = SCHINESE_PINYIN_M ') <NLSSORT ('bar', 'nls _ SORT = done') 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 ASV_COMPARE VARCHAR2 (100); V_RETURN VARCHAR2 (4000); FUNCTION F_NLSSORT (P_WORD IN VARCHAR2) RETURN VARCHAR2 asbeginreturn nlssort (P_WORD, 'nls _ SORT = SCHINESE_PINYIN_M '); END; BEGIN -www.2cto.com-for I IN 1 .. LENGTH (P_NAME) LOOPV_COMPARE: = F_NLSSORT (SUBSTR (P_NAME, I, 1); IF V_COMPARE> = F_NLSSORT ('tag') AND V_COMPARE <= F_NLSSORT ('tag ') THENV_RETURN: = V_RETURN | 'a'; ELSIF V_COMPARE> = F_NLSSORT ('8') AND V_COMPARE <= F_NLSSORT (' ') THENV_RETURN: = V_RETURN |' B '; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'C'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou ') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'D '; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou ') THENV_RETURN: = V_RETURN | 'E'; ELSIF V_COMPARE> = F_NLSSORT ('put') AND V_COMPARE <= F_NLSSORT ('put') THENV_RETURN: = V_RETURN | 'F '; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'G'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou ') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'H'; ELSIF V_COMPARE> = F_NLSSORT (') AND V_COMPARE <= F_NLSSORT ('hangzhou ') THENV_RETURN: = V_RETURN | 'J'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'K '; ELSIF V_COMPARE> = F_NLSSORT ('shanghai') AND V_COMPARE <= F_NLSSORT ('shanghai') THENV_RETURN: = V_RETURN | 'l'; ELSIF V_COMPARE> = F_NLSSORT ('shanghai ') AND V_COMPARE <= F_NLSSORT ('hour') THENV_RETURN: = V_RETURN | 'M'; ELSIF V_COMPARE> = F_NLSSORT ('hour') AND V_COMPARE <= F_NLSSORT ('hour ') THENV_RETURN: = V_RETURN | 'n'; ELSIF V_COMPARE> = F_NLSSORT ('hour') AND V_COMPARE <= F_NLSSORT ('hour') THENV_RETURN: = V_RETURN | 'O '; ELSIF V_COMPARE> = F_NLSSORT ('taobao') AND V_COMPARE <= F_NLSSORT ('overview') THENV_RETURN: = V_RETURN | 'P'; ELSIF V_COMPARE> = F_NLSSORT ('7 ') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'q'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou ') THENV_RETURN: = V_RETURN | 'R'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN |'s '; ELSIF V_COMPARE> = F_NLSSORT ('shanghai') AND V_COMPARE <= F_NLSSORT ('shanghai') THENV_RETURN: = V_RETURN | 'T'; ELSIF V_COMPARE> = F_NLSSORT ('shanghai ') AND V_COMPARE <= F_NLSSORT ('hangzhou') THENV_RETURN: = V_RETURN | 'W'; ELSIF V_COMPARE> = F_NLSSORT ('hangzhou') AND V_COMPARE <= F_NLSSORT ('hangzhou ') THENV_RETURN: = V_RETURN | 'X'; ELSIF V_COMPARE> = F_NLSSORT ('ya ') AND V_COMPARE <= F_NLSSORT ('y') THENV_RETURN: = V_RETURN | 'y '; ELSIF V_COMPARE> = F_NLSSORT ('hour') AND V_COMPARE <= F_NLSSORT ('hour') THENV_RETURN: = V_RETURN | 'Z'; end if; end loop; RETURN V_RETURN; END; test: SELECT F_TRANS_PINYIN_CAPITAL ('wang defeng') from dual returns the first letter: the author of wdf szwangdf.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.