oracle汉字转拼音首字母函数

本内容来源网络,源自哪里我给忘了φ(゜▽゜*)?。

我相信,大佬一定会原谅我的O(∩_∩)O

正题如下

  • 这是个oracle函数,在sql窗口或者放在脚本里执行一下就可以开心的调用了o(* ̄▽ ̄*)ブ;
    • 调用引例:

      SELECT HANZI_TO_PINYIN(‘李琛‘) FROM DUAL;
      

      dual最小表,只返回一条记录,目的就是维持句子语法完整。

      函数体如下:

      CREATE OR REPLACE FUNCTION HANZI_TO_PINYIN (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;

oracle汉字转拼音首字母函数

上一篇:centos7下安装mysql6初始化安装密码


下一篇:pycharm远程连接vagrant虚拟机中mariadb数据库