CREATE OR REPLACE FUNCTION PUB_GET_PY(p_zh_char CHAR DEFAULT '%') RETURN CHAR IS /******************************************************************** 过程名称：PUB_GET_PY 功能：生成汉字拼音的首字符 ********************************************************************/ ---v_汉字内码 v_zh_char_code NUMBER(8); ---v_拼音 v_py CHAR(1); BEGIN v_zh_char_code := ASCII(p_zh_char); IF v_zh_char_code BETWEEN 45217 AND 45252 THEN v_py := 'A'; ELSIF v_zh_char_code BETWEEN 45253 AND 45760 THEN v_py := 'B'; ELSIF v_zh_char_code BETWEEN 45761 AND 46317 THEN v_py := 'C'; ELSIF v_zh_char_code BETWEEN 46318 AND 46825 THEN v_py := 'D'; ELSIF v_zh_char_code BETWEEN 46826 AND 47009 THEN v_py := 'E'; ELSIF v_zh_char_code BETWEEN 47010 AND 47296 THEN v_py := 'F'; ELSIF v_zh_char_code BETWEEN 47297 AND 47613 THEN v_py := 'G'; ELSIF v_zh_char_code BETWEEN 47614 AND 48116 THEN v_py := 'H'; ELSIF v_zh_char_code BETWEEN 48117 AND 49061 THEN v_py := 'J'; ELSIF v_zh_char_code BETWEEN 49062 AND 49323 THEN v_py := 'K'; ELSIF v_zh_char_code BETWEEN 49324 AND 49895 THEN v_py := 'L'; ELSIF v_zh_char_code BETWEEN 49896 AND 50370 THEN v_py := 'M'; ELSIF v_zh_char_code BETWEEN 50371 AND 50613 THEN v_py := 'N'; ELSIF v_zh_char_code BETWEEN 50614 AND 50621 THEN v_py := 'O'; ELSIF v_zh_char_code BETWEEN 50622 AND 50925 THEN v_py := 'P'; ELSIF v_zh_char_code BETWEEN 50926 AND 51386 THEN v_py := 'Q'; ELSIF v_zh_char_code BETWEEN 51387 AND 51445 THEN v_py := 'R'; ELSIF v_zh_char_code BETWEEN 51446 AND 52217 THEN v_py := 'S'; ELSIF v_zh_char_code BETWEEN 52218 AND 52697 THEN v_py := 'T'; ELSIF v_zh_char_code BETWEEN 52698 AND 52979 THEN v_py := 'W'; ELSIF v_zh_char_code BETWEEN 52980 AND 53640 THEN v_py := 'X'; ELSIF v_zh_char_code BETWEEN 53641 AND 54480 THEN v_py := 'Y'; ELSIF v_zh_char_code BETWEEN 54481 AND 55289 THEN v_py := 'Z'; ELSE v_py := NULL; END IF; RETURN v_py; END PUB_GET_PY;
提取汉字字符串的拼音的首字母，其中去掉空格保留原有字母和数字：
create or replace FUNCTION PUB_GET_STRING_PY(strs varchar2) return varchar2 is /*********************************** 过程名称：PUB_GET_STRING_PY 功能：生成汉字字符串的拼音首字符 **********************************/ v_length number; v_char varchar2(2); v_charlength number; v_charPY varchar2(2); i int:=1; myresult varchar2(500):=''; begin --获取字符串长度 v_length:=length(strs); while i<=v_length loop --按顺序取得字符 v_char:=substr(strs,i,1); v_charlength:=lengthb(rtrim(v_char)); v_charPY:=rtrim(v_char); --判断是否是汉字 if v_charlength=2 then v_charPY:=pub_get_py(rtrim(v_char)); end if; --将拼音进行连接 myresult:=myresult || v_charPY; i:=i+1; end loop; return myresult; end PUB_GET_STRING_PY;
方法二： 获取拼音简码函数
CREATE OR REPLACE FUNCTION GET_PYJM (P_NAME IN VARCHAR2) RETURN VARCHAR2 AS V_COMPARE VARCHAR2 (100); V_RETURN VARCHAR2 (4000); BEGIN DECLARE 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_RETURN || 'E'; ELSIF V_COMPARE >= F_NLSSORT ('发') AND V_COMPARE <= F_NLSSORT ('猤') THEN V_RETURN := V_RETURN || 'F'; ELSIF V_COMPARE >= F_NLSSORT ('旮') AND V_COMPARE <= F_NLSSORT ('腂') 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 ('丌') AND V_COMPARE <= F_NLSSORT ('攈') THEN V_RETURN := V_RETURN || 'J'; ELSIF V_COMPARE >= F_NLSSORT ('咔') AND V_COMPARE <= F_NLSSORT ('穒') THEN V_RETURN := V_RETURN || 'K'; ELSIF V_COMPARE >= F_NLSSORT ('垃') 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 ('瘧') 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 ('曝') THEN V_RETURN := V_RETURN || 'P'; ELSIF V_COMPARE >= F_NLSSORT ('七') AND V_COMPARE <= F_NLSSORT ('裠') THEN V_RETURN := V_RETURN || 'Q'; ELSIF V_COMPARE >= F_NLSSORT ('亽') AND V_COMPARE <= F_NLSSORT ('鶸') THEN V_RETURN := V_RETURN || 'R'; ELSIF V_COMPARE >= F_NLSSORT ('仨') 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 ('夕') AND V_COMPARE <= F_NLSSORT ('鑂') THEN V_RETURN := V_RETURN || 'X'; ELSIF V_COMPARE >= F_NLSSORT ('丫') AND V_COMPARE <= F_NLSSORT ('韻') THEN V_RETURN := V_RETURN || 'Y'; ELSIF V_COMPARE >= F_NLSSORT ('帀') AND V_COMPARE <= F_NLSSORT ('咗') THEN V_RETURN := V_RETURN || 'Z'; END IF; END LOOP; RETURN V_RETURN; END; END;