oracle汉字拼音的首字母.docVIP

oracle汉字拼音的首字母 2009-08-07 ORACLE函数获取汉字拼音首字母 /*------增加：yl--*/ oracle 中 NLSSORT函数的用法 NLSSORT()，用来进行语言排序 拼音 SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_PINYIN_M) 笔划 SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_STROKE_M) 部首 SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,NLS_SORT = SCHINESE_RADICAL_M) /*------增加：yl end--*/ 列表一行汉字 WITH A AS (SELECT 获取汉字拼音首字母 W FROM DUAL) SELECT SUBSTR(W, ROWNUM, 1) FROM A CONNECT BY ROWNUM = (SELECT LENGTH(W) FROM A); 返回结果 获 取 汉 字 拼 音 首 字 母 用汉字字符集对这个列表进行排序 WITH A AS (SELECT 获取汉字拼音首字母 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); 返回结果 汉 获 母 拼 取 首 音 字 字 那么根据这个原理，上面输入一个拼音A打头的字澳，后面输入一个B大头的字吧，找到每个音节的起止的汉字是哪个 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(澳, NLS_SORT=SCHINESE_PINYIN_M) AND NLSSORT(C, NLS_SORT=SCHINESE_PINYIN_M) NLSSORT(吧, NLS_SORT=SCHINESE_PINYIN_M) ORDER BY NLSSORT(C, NLS_SORT=SCHINESE_PINYIN_M); 根据返回的结果，就能看到，A的结束和B的开始的汉子分别是：“驁” “八”，依次类推可以找到其他的分界点，那么最后的函数就是如下： 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( 吖 ) AND V_COMPARE = F_NLSSORT(驁 ) THEN V_RETURN := V_RETURN || a; ELSIF V_COMPARE = F_NLSSORT(八 ) AND V_COMPARE = F_NLSSORT(簿 ) THEN V_RETURN := V_RETURN || b; ELSIF V_COMPARE = F_NLSSORT(嚓 ) AND V_COMPARE = F_NLSSORT(錯 ) 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_RET