Oracle取得中文拼音首字母函数

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;