oracle 提取中文字符串拼音首字母函数，拼音简码提取函数

从别的数据库里拿到的获取拼音简码的函数，觉得挺有意思的，具体的思路就是，通过oracle的NLSSORT函数对汉字按照拼音排序，然后根据汉字的区间返回对应的首字母。具体实现效果和代码如下。

希望能帮助到你

select GET_PYJM('赵鹏') from dual;

1 ZP

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;