C#、Oracle、Sqlserver中拼音查询的函数

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

C# sqlserver oracle 的都有不过发现那个 C# 的好像"楠"字查的时候会有问题。。。不知道为啥。。

C#

Oracle

1create or replace function fGetPy
2(V_Str varchar2)
3return varchar2
4as
5  v_strlen int;
6  v_return varchar2(500);
7  v_ii int    ;
8  v_n int;
9  v_c char(1);
10  v_chn varchar2(2);
11  V_RC varchar2(500);
12begin
13 V_RC:=V_Str;
14
15 v_strlen :=len(V_RC);
16 v_return := '';
17 v_ii:=0;
18 while v_ii<v_strlen loop
19  v_ii:=v_ii+1;
20  v_n:=63;
21  SELECT substring(V_RC,v_ii,1) INTO v_chn FROM DUAL;
22
23
24  select v_n+max(rowsf) into v_n
25   from(
26   select chn,ROWNUM rowsf from(
27    select chn from (
28     select '吖'  chn  from dual
29     union  select '八'   from dual
30     union all  select '嚓'  from dual
31     union all  select '咑'   from dual
32     union all  select '妸'    from dual
33     union all select '发'    from dual
34     union all select '旮'   from dual
35     union all select '铪'   from dual
36     union all select '丌'  from dual--because have no 'i'
37     union all select '丌'   from dual
38     union all select '咔'    from dual
39     union all select '垃'   from dual
40     union all select '嘸'   from dual
41     union all select '拏'    from dual
42     union all select '噢'   from dual
43     union all select '妑'   from dual
44     union all select '七'   from dual
45     union all select '呥'    from dual
46     union all select '仨'    from dual
47     union all select '他'    from dual
48     union all select '屲'  from dual
49     union all select '屲'  from dual
50     union all select '屲' from dual
51     union all select '夕'  from dual
52     union all select '丫'  from dual
53     union all select '帀'    from dual
54     union all select v_chn from dual
55     )  a
56   order by nlssort(chn,'NLS_SORT=SCHINESE_PINYIN_M')
57   ) c
58    )  b WHERE chn=v_chn ;
59
60
61  v_c:=chr(v_n);
62  if chr(v_n) ='@' then--英文直接返回
63   v_c:=v_chn    ;
64   end if;
65
66
67  v_return:=v_return||v_c;
68 end loop;
69
70 return v_return;
71end;

sql server

1create   function   fGetPy(@Str   varchar(500)='')
2  returns   varchar(500)
3  as
4  begin
5  declare   @strlen   int,@return   varchar(500),@ii   int
6  declare   @n   int,@c   char(1),@chn   nchar(1)
7
8  select   @strlen=len(@str),@return='',@ii=0
9  set   @ii=0
10  while   @ii<@strlen
11  begin
12  select   @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
13  select   @n   =   @n   +1
14  ,@c   =   case   chn   when   @chn   then   char(@n)   else   @c   end
15  from(
16  select   top   27   *   from   (
17  select   chn   =   '吖'
18  union   all   select   '八'
19  union   all   select   '嚓'
20  union   all   select   '咑'
21  union   all   select   '妸'
22  union   all   select   '发'
23  union   all   select   '旮'
24  union   all   select   '铪'
25  union   all   select   '丌' --because   have   no   'i'
26  union   all   select   '丌'
27  union   all   select   '咔'
28  union   all   select   '垃'
29  union   all   select   '嘸'
30  union   all   select   '拏'
31  union   all   select   '噢'
32  union   all   select   '妑'
33  union   all   select   '七'
34  union   all   select   '呥'
35  union   all   select   '仨'
36  union   all   select   '他'
37  union   all   select   '屲' --no   'u'
38  union   all   select   '屲' --no   'v'
39  union   all   select   '屲'
40  union   all   select   '夕'
41  union   all   select   '丫'
42  union   all   select   '帀'
43  union   all   select   @chn)   as   a
44  order   by   chn   COLLATE   Chinese_PRC_CI_AS
45  )   as   b
46  set   @return=@return+@c
47  end
48  return(@return)
49  end