oracle 提取中文字符串拼音首字母函数,拼音简码提取函数-阿里云开发者社区

开发者社区> 数据库> 正文

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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章