C#、Oracle、Sql server中拼音查询的函数
C# sqlserver oracle 的都有 不过发现那个 C# 的好像"楠"字查的时候会有问题。。。不知道为啥。。
C#
Oracle
sql server
C#
1/// <summary>
2 /// 生成拼音简码
3 /// </summary>
4 /// <param name="unicodeString">Unicode编码字符串</param>
5 /// <returns>拼音简码:string</returns>
6 public static string GetPinyinCode(string unicodeString)
7 {
8 int i = 0;
9 ushort key = 0;
10 string strResult = string.Empty;
11 //创建两个不同的encoding对象
12 Encoding unicode = Encoding.Unicode;
13 //创建GBK码对象
14 Encoding gbk = Encoding.GetEncoding(936);
15 //将unicode字符串转换为字节
16 byte[] unicodeBytes = unicode.GetBytes(unicodeString);
17 //再转化为GBK码
18 byte[] gbkBytes = Encoding.Convert(unicode, gbk, unicodeBytes);
19 while (i < gbkBytes.Length)
20 {
21 //如果为数字\字母\其他ASCII符号
22 if (gbkBytes[i] <= 127)
23 {
24 strResult = strResult + (char)gbkBytes[i];
25 i++;
26 }
27 #region 否则生成汉字拼音简码,取拼音首字母
28 else
29 {
30
31 key = (ushort)(gbkBytes[i] * 256 + gbkBytes[i + 1]);
32 if (key >= '\uB0A1' && key <= '\uB0C4')
33 {
34 strResult = strResult + "A";
35 }
36 else if (key >= '\uB0C5' && key <= '\uB2C0')
37 {
38 strResult = strResult + "B";
39 }
40 else if (key >= '\uB2C1' && key <= '\uB4ED')
41 {
42 strResult = strResult + "C";
43 }
44 else if (key >= '\uB4EE' && key <= '\uB6E9')
45 {
46 strResult = strResult + "D";
47 }
48 else if (key >= '\uB6EA' && key <= '\uB7A1')
49 {
50 strResult = strResult + "E";
51 }
52 else if (key >= '\uB7A2' && key <= '\uB8C0')
53 {
54 strResult = strResult + "F";
55 }
56 else if (key >= '\uB8C1' && key <= '\uB9FD')
57 {
58 strResult = strResult + "G";
59 }
60 else if (key >= '\uB9FE' && key <= '\uBBF6')
61 {
62 strResult = strResult + "H";
63 }
64 else if (key >= '\uBBF7' && key <= '\uBFA5')
65 {
66 strResult = strResult + "J";
67 }
68 else if (key >= '\uBFA6' && key <= '\uC0AB')
69 {
70 strResult = strResult + "K";
71 }
72 else if (key >= '\uC0AC' && key <= '\uC2E7')
73 {
74 strResult = strResult + "L";
75 }
76 else if (key >= '\uC2E8' && key <= '\uC4C2')
77 {
78 strResult = strResult + "M";
79 }
80 else if (key >= '\uC4C3' && key <= '\uC5B5')
81 {
82 strResult = strResult + "N";
83 }
84 else if (key >= '\uC5B6' && key <= '\uC5BD')
85 {
86 strResult = strResult + "O";
87 }
88 else if (key >= '\uC5BE' && key <= '\uC6D9')
89 {
90 strResult = strResult + "P";
91 }
92 else if (key >= '\uC6DA' && key <= '\uC8BA')
93 {
94 strResult = strResult + "Q";
95 }
96 else if (key >= '\uC8BB' && key <= '\uC8F5')
97 {
98 strResult = strResult + "R";
99 }
100 else if (key >= '\uC8F6' && key <= '\uCBF9')
101 {
102 strResult = strResult + "S";
103 }
104 else if (key >= '\uCBFA' && key <= '\uCDD9')
105 {
106 strResult = strResult + "T";
107 }
108 else if (key >= '\uCDDA' && key <= '\uCEF3')
109 {
110 strResult = strResult + "W";
111 }
112 else if (key >= '\uCEF4' && key <= '\uD188')
113 {
114 strResult = strResult + "X";
115 }
116 else if (key >= '\uD1B9' && key <= '\uD4D0')
117 {
118 strResult = strResult + "Y";
119 }
120 else if (key >= '\uD4D1' && key <= '\uD7F9')
121 {
122 strResult = strResult + "Z";
123 }
124 else
125 {
126 strResult = strResult + "?";
127 }
128 i = i + 2;
129 }
130 #endregion
131 }//end while
132
133 return strResult;
134 }
2 /// 生成拼音简码
3 /// </summary>
4 /// <param name="unicodeString">Unicode编码字符串</param>
5 /// <returns>拼音简码:string</returns>
6 public static string GetPinyinCode(string unicodeString)
7 {
8 int i = 0;
9 ushort key = 0;
10 string strResult = string.Empty;
11 //创建两个不同的encoding对象
12 Encoding unicode = Encoding.Unicode;
13 //创建GBK码对象
14 Encoding gbk = Encoding.GetEncoding(936);
15 //将unicode字符串转换为字节
16 byte[] unicodeBytes = unicode.GetBytes(unicodeString);
17 //再转化为GBK码
18 byte[] gbkBytes = Encoding.Convert(unicode, gbk, unicodeBytes);
19 while (i < gbkBytes.Length)
20 {
21 //如果为数字\字母\其他ASCII符号
22 if (gbkBytes[i] <= 127)
23 {
24 strResult = strResult + (char)gbkBytes[i];
25 i++;
26 }
27 #region 否则生成汉字拼音简码,取拼音首字母
28 else
29 {
30
31 key = (ushort)(gbkBytes[i] * 256 + gbkBytes[i + 1]);
32 if (key >= '\uB0A1' && key <= '\uB0C4')
33 {
34 strResult = strResult + "A";
35 }
36 else if (key >= '\uB0C5' && key <= '\uB2C0')
37 {
38 strResult = strResult + "B";
39 }
40 else if (key >= '\uB2C1' && key <= '\uB4ED')
41 {
42 strResult = strResult + "C";
43 }
44 else if (key >= '\uB4EE' && key <= '\uB6E9')
45 {
46 strResult = strResult + "D";
47 }
48 else if (key >= '\uB6EA' && key <= '\uB7A1')
49 {
50 strResult = strResult + "E";
51 }
52 else if (key >= '\uB7A2' && key <= '\uB8C0')
53 {
54 strResult = strResult + "F";
55 }
56 else if (key >= '\uB8C1' && key <= '\uB9FD')
57 {
58 strResult = strResult + "G";
59 }
60 else if (key >= '\uB9FE' && key <= '\uBBF6')
61 {
62 strResult = strResult + "H";
63 }
64 else if (key >= '\uBBF7' && key <= '\uBFA5')
65 {
66 strResult = strResult + "J";
67 }
68 else if (key >= '\uBFA6' && key <= '\uC0AB')
69 {
70 strResult = strResult + "K";
71 }
72 else if (key >= '\uC0AC' && key <= '\uC2E7')
73 {
74 strResult = strResult + "L";
75 }
76 else if (key >= '\uC2E8' && key <= '\uC4C2')
77 {
78 strResult = strResult + "M";
79 }
80 else if (key >= '\uC4C3' && key <= '\uC5B5')
81 {
82 strResult = strResult + "N";
83 }
84 else if (key >= '\uC5B6' && key <= '\uC5BD')
85 {
86 strResult = strResult + "O";
87 }
88 else if (key >= '\uC5BE' && key <= '\uC6D9')
89 {
90 strResult = strResult + "P";
91 }
92 else if (key >= '\uC6DA' && key <= '\uC8BA')
93 {
94 strResult = strResult + "Q";
95 }
96 else if (key >= '\uC8BB' && key <= '\uC8F5')
97 {
98 strResult = strResult + "R";
99 }
100 else if (key >= '\uC8F6' && key <= '\uCBF9')
101 {
102 strResult = strResult + "S";
103 }
104 else if (key >= '\uCBFA' && key <= '\uCDD9')
105 {
106 strResult = strResult + "T";
107 }
108 else if (key >= '\uCDDA' && key <= '\uCEF3')
109 {
110 strResult = strResult + "W";
111 }
112 else if (key >= '\uCEF4' && key <= '\uD188')
113 {
114 strResult = strResult + "X";
115 }
116 else if (key >= '\uD1B9' && key <= '\uD4D0')
117 {
118 strResult = strResult + "Y";
119 }
120 else if (key >= '\uD4D1' && key <= '\uD7F9')
121 {
122 strResult = strResult + "Z";
123 }
124 else
125 {
126 strResult = strResult + "?";
127 }
128 i = i + 2;
129 }
130 #endregion
131 }//end while
132
133 return strResult;
134 }
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;
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
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