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_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 ;
SELECT F_TRANS_PINYIN_CAPITAL ( ‘湖北信业建设项目管理有限公司‘ ) FROM dual;