winform用户输入查询与拼音首字母的结合,提高用户的操作体验
来源:互联网 发布:客户信息管理系统源码 编辑:程序博客网 时间:2024/06/05 14:31
在Winform界面中,同样也可以做到智能提示,由于Winform中的响应速度比较快,我们可以根据输入的首字母或者部分中文快速更新列表内容即可,如下图所示。
其实以上两个,都是需要一个Sql函数,就是把中文转换为首字母的函数,以便能够快速搜索内容,下面我列出SqlServer和Oracle的转换首字母的函数。以飨读者。
SqlServer的汉字转拼音码的函数:
代码
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->--
-- Definition for user-defined function f_GetPy :
--
GO
create function [dbo].f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖 ','A' union all select '八','B' union all
select '嚓 ','C' union all select '咑','D' union all
select '妸 ','E' union all select '发','F' union all
select '旮 ','G' union all select '铪','H' union all
select '丌 ','J' union all select '咔','K' union all
select '垃 ','L' union all select '嘸','M' union all
select '拏 ','N' union all select '噢','O' union all
select '妑 ','P' union all select '七','Q' union all
select '呥 ','R' union all select '仨','S' union all
select '他 ','T' union all select '屲','W' union all
select '夕 ','X' union all select '丫','Y' union all
select '帀 ','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr <=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->--
-- Definition for user-defined function f_GetPy :
--
GO
create function [dbo].f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖 ','A' union all select '八','B' union all
select '嚓 ','C' union all select '咑','D' union all
select '妸 ','E' union all select '发','F' union all
select '旮 ','G' union all select '铪','H' union all
select '丌 ','J' union all select '咔','K' union all
select '垃 ','L' union all select '嘸','M' union all
select '拏 ','N' union all select '噢','O' union all
select '妑 ','P' union all select '七','Q' union all
select '呥 ','R' union all select '仨','S' union all
select '他 ','T' union all select '屲','W' union all
select '夕 ','X' union all select '丫','Y' union all
select '帀 ','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr <=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
Oracle的汉字转拼音首字母的函数:
代码
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->CREATE ORREPLACEFUNCTION F_PINYIN(P_NAMEINVARCHAR2)RETURNVARCHAR2 AS
V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
FUNCTION F_NLSSORT(P_WORDINVARCHAR2)RETURNVARCHAR2 AS
BEGIN
RETURN NLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1..NVL(LENGTH(P_NAME),0) 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;
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->CREATE ORREPLACEFUNCTION F_PINYIN(P_NAMEINVARCHAR2)RETURNVARCHAR2 AS
V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
FUNCTION F_NLSSORT(P_WORDINVARCHAR2)RETURNVARCHAR2 AS
BEGIN
RETURN NLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1..NVL(LENGTH(P_NAME),0) 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;
使用代码大概如下所示:
使用例子1:
代码
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> ///<summary>
/// 根据商品名称获取商品列表
/// </summary>
/// <param name="goodsType">商品类型</param>
/// <returns></returns>
public List<GoodsInfo> FindByName(string goodsName)
{
string sql=string.Format("Name like '%{0}%' or dbo.f_GetPy(Name) like '{0}%'", goodsName);
returnthis.Find(sql);
}
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> ///<summary>
/// 根据商品名称获取商品列表
/// </summary>
/// <param name="goodsType">商品类型</param>
/// <returns></returns>
public List<GoodsInfo> FindByName(string goodsName)
{
string sql=string.Format("Name like '%{0}%' or dbo.f_GetPy(Name) like '{0}%'", goodsName);
returnthis.Find(sql);
}
- winform中用户输入查询与拼音首字母的结合,提高用户的操作体验
- winform用户输入查询与拼音首字母的结合,提高用户的操作体验
- 用户输入查询与拼音首字母的结合,提高用户的操作体验
- 【整理】输入导航功能-查询与拼音首字母的结合以提高用户体验
- 提高用户体验的几个方法
- 怎样提高网站的用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 提高用户体验的图片格式progressive jpeg
- 一些提高用户体验的前端技术
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- 巧用Ajax的beforeSend 提高用户体验
- spring 3.1.0.m 包详解
- TabHost用selector时遮住选项卡的文字
- 2012年1月5日 星期四 (oracle读书笔记,PL/SQL异常错误处理)
- winform中用户输入查询与拼音首字母的结合,提高用户的操作体验
- OpenFire、Spark、Smack介绍
- winform用户输入查询与拼音首字母的结合,提高用户的操作体验
- C语言中printf函数自增自减、入栈和输出关系。
- ArcGIS Server 9.3 Java安装部署教程
- java多线程例子
- Javascript代码压缩、加密算法的破解分析及工具实现
- JAVA的网络编程【转】
- thinking in java笔记 8 多态
- Java编程思想精彩评注分享之一
- java串口通信 (转)