C#、Oracle、Sql server中拼音查询的函数
来源:互联网 发布:河北广电网络集团招聘 编辑:程序博客网 时间:2024/05/21 15:47
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
阅读全文
0 0
- C#、Oracle、Sql server中拼音查询的函数
- C#、Oracle、Sql server中拼音查询的函数
- SQL Server中取汉字拼音的函数
- SQL Server中取汉字拼音的函数
- 经典SQL Server用户自定义函数:首字拼音查询
- 经典SQL Server用户自定义函数:首字拼音查询
- Oracle中获取汉字的拼音函数
- 一个sql server中用到的函数,用来取拼音
- sql server中用到的函数,用来取拼音
- sql server自动生成拼音首字母的函数
- sql server自动生成拼音首字母的函数 .
- sql server中类似oracle中decode功能的函数
- 【SQL Server数据迁移】32位的机器:SQL Server中查询ORACLE的数据
- 【SQL Server数据迁移】64位的机器:SQL Server中查询ORACLE的数据
- SQL SERVER数据库中取汉字的拼音首字母
- SQL Server中模拟Oracle的to_date函数
- js、sql server、oracle中时间函数的简单简介
- SQL SERVER 中的拼音码转换函数
- 统计学简介之十——样本量的确定
- SQL2005转2000
- Banner实现图片轮播
- java mybatis 配置
- 编译原理与编译构造 目录
- C#、Oracle、Sql server中拼音查询的函数
- 0916 T4 赛小城学数学
- PAT 甲级 1016. Phone Bills (25)
- 华为中级机试题--二分查找树的应用
- HDU 1753 大明A+B java大数
- 奋斗群群赛10---次次诡异
- 第二周项目 1 -函数参数传递的三种方式
- eclipse中配置DTD验证
- python 文件查操作汇总