在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
复制代码
Oracle的汉字转拼音首字母的函数:
代码
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->CREATE ORREPLACE
FUNCTION F_PINYIN(P_NAMEIN
VARCHAR2)RETURN
VARCHAR2
AS
V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
FUNCTION F_NLSSORT(P_WORDIN
VARCHAR2)RETURN
VARCHAR2
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);
}
复制代码
转载:http://www.cnblogs.com/wuhuacong/archive/2010/01/25/1655916.html