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

来源：互联网发布：原始股购买知乎编辑：程序博客网时间：2024/05/28 22:08
从别的数据库里拿到的获取拼音简码的函数，觉得挺有意思的，具体的思路就是，通过oracle的NLSSORT函数对汉字按照拼音排序，然后根据汉字的区间返回对应的首字母。具体实现效果和代码如下。
希望能帮助到你
/* 获取拼音简码函数 */CREATE OR REPLACE FUNCTION GET_PYJM (P_NAME IN VARCHAR2)    RETURN VARCHAR2AS    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;
1 0