sql获取汉字的首字母

  1 ------------------------------------sql server 版本------------------------------------------
  2 --
  3 -- Definition for user-defined function f_GetPy : 
  4 --
  5 GO
  6 create   function   [dbo].f_GetPy(@str   nvarchar(4000)) 
  7 returns   nvarchar(4000) 
  8 as 
  9 begin 
 10 declare   @strlen   int,@re   nvarchar(4000) 
 11 declare   @t   table(chr   nchar(1)   collate   Chinese_PRC_CI_AS,letter   nchar(1)) 
 12 insert   into   @t(chr,letter) 
 13     select   '吖 ', 'A '   union   all   select   '八 ', 'B '   union   all 
 14     select   '嚓 ', 'C '   union   all   select   '咑 ', 'D '   union   all 
 15     select   '妸 ', 'E '   union   all   select   '发 ', 'F '   union   all 
 16     select   '旮 ', 'G '   union   all   select   '铪 ', 'H '   union   all 
 17     select   '丌 ', 'J '   union   all   select   '咔 ', 'K '   union   all 
 18     select   '垃 ', 'L '   union   all   select   '嘸 ', 'M '   union   all 
 19     select   '拏 ', 'N '   union   all   select   '噢 ', 'O '   union   all 
 20     select   '妑 ', 'P '   union   all   select   '七 ', 'Q '   union   all 
 21     select   '呥 ', 'R '   union   all   select   '仨 ', 'S '   union   all 
 22     select   '他 ', 'T '   union   all   select   '屲 ', 'W '   union   all 
 23     select   '夕 ', 'X '   union   all   select   '丫 ', 'Y '   union   all 
 24     select   '帀 ', 'Z ' 
 25     select   @strlen=len(@str),@re= '' 
 26     while   @strlen> 0 
 27     begin 
 28         select   top   1   @re=letter+@re,@strlen=@strlen-1 
 29             from   @t   a   where   chr <=substring(@str,@strlen,1) 
 30             order   by   chr   desc 
 31         if   @@rowcount=0 
 32             select   @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 
 33     end 
 34     return(@re) 
 35 end
 36 
 37 
 38 
 39 
 40 
 41 ---------------------------------orecal 版本-------------------------------------
 42 CREATE OR REPLACE FUNCTION F_PINYIN(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
 43      V_COMPARE VARCHAR2(100);
 44      V_RETURN VARCHAR2(4000);
 45 
 46      FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
 47      BEGIN
 48       RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
 49      END;
 50     BEGIN
 51 
 52     FOR I IN 1..NVL(LENGTH(P_NAME), 0) LOOP
 53      V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
 54      IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
 55       V_RETURN := V_RETURN || 'A';
 56      ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
 57       V_RETURN := V_RETURN || 'B';
 58      ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
 59       V_RETURN := V_RETURN || 'C';
 60      ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
 61       V_RETURN := V_RETURN || 'D';
 62      ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
 63       V_RETURN := V_RETURN || 'E';
 64      ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
 65       V_RETURN := V_RETURN || 'F';
 66      ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
 67       V_RETURN := V_RETURN || 'G';
 68      ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
 69       V_RETURN := V_RETURN || 'H';
 70      ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
 71       V_RETURN := V_RETURN || 'J';
 72      ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
 73       V_RETURN := V_RETURN || 'K';
 74      ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
 75       V_RETURN := V_RETURN || 'L';
 76      ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
 77       V_RETURN := V_RETURN || 'M';
 78      ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
 79       V_RETURN := V_RETURN || 'N';
 80      ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
 81       V_RETURN := V_RETURN || 'O';
 82      ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN
 83       V_RETURN := V_RETURN || 'P';
 84      ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN
 85       V_RETURN := V_RETURN || 'Q';
 86      ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN
 87       V_RETURN := V_RETURN || 'R';
 88      ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN
 89       V_RETURN := V_RETURN || 'S';
 90      ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN
 91       V_RETURN := V_RETURN || 'T';
 92      ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN
 93       V_RETURN := V_RETURN || 'W';
 94      ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN
 95       V_RETURN := V_RETURN || 'X';
 96      ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN
 97       V_RETURN := V_RETURN || 'Y';
 98      ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN
 99       V_RETURN := V_RETURN || 'Z';
100      END IF;
101     END LOOP;
102     RETURN V_RETURN;
103    END;

 

转载于:https://www.cnblogs.com/lmlblog/archive/2011/11/23/2259853.html