--创建取拼音函数 create function [dbo].[fGetPy](@Str varchar(500)='') returns varchar(500) as begin declare @strlen int,@return varchar(500),@ii int declare @n int,@c char(1),@chn nchar(1)select @strlen=len(@str),@return='',@ii=0 set @ii=0 while @ii<@strlen begin select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1) if @chn>'z' select @n = @n +1 ,@c = case chn when @chn then char(@n) else @c end from( select top 27 * from ( select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' --because have no 'i' union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' --no 'u' union all select '屲' --no 'v' union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b else set @c='a' set @return=@return+@c end return(@return) ENDSELECT '哈尔滨' '汉字',dbo.fGetPy('哈尔滨') '拼音 ' /* 结果 汉字 拼音 ------ ----------- 哈尔滨 HEB */
declare @table table (id int,name varchar(6)) insert into @table select 1,'哈尔滨' union all select 2,'上海' union all select 3,'北京' union all select 4,'天津' union all select 5,'广州' union all select 6,'南京' union all select 7,'无锡' union all select 8,'武汉' union all select 9,'长沙' SELECT * FROM @table /* id name ----------- ------ 1 哈尔滨 2 上海 3 北京 4 天津 5 广州 6 南京 7 无锡 8 武汉 9 长沙 */ DECLARE @char nvarchar(200) --定义条件 SET @char='w' SELECT bb.name FROM ( select *, dbo.fGetPy(name) AS py from @table ) bb WHERE substring(py,1,1)=@char/* name ------ 无锡 武汉 */
new Autocompleter.Local( "bands", "band_list", [ "ABBA", "AC/DC", "Bay City Rollers", "Black Sabbath", "Boston", "Lynyrd Skynyrd", "Pink Floyd", "Queen", "Ramones", "The Village People", "Yes" ], { tokens: "," } ); // ]]> </script></body> </html>
CREATE table m_table(id int,name varchar(60)) insert into m_table select 1,'哈尔滨' union all select 2,'上海' union all select 3,'北京' union all select 4,'天津' union all select 5,'广州' union all select 6,'南京' union all select 7,'无锡' union all select 8,'武汉' union all select 9,'长沙' SELECT * FROM m_tableCREATE PROC GetMyDate ( @char nvarchar(200) ) as begin SELECT bb.name FROM ( select *, dbo.fGetPy(name) AS py from m_table ) bb WHERE py LIKE '%'+@char+'%' end--执行存储过程 exec GetMyDate 'w' exec GetMyDate 'wh'
所有的a 的汉字
只是列出了常用的几个这就是AJAX的典型应用啊
去学学AJAX就可以了
--创建取拼音函数
create function [dbo].[fGetPy](@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '嘸'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
else set @c='a'
set @return=@return+@c
end
return(@return)
ENDSELECT '哈尔滨' '汉字',dbo.fGetPy('哈尔滨') '拼音 '
/*
结果
汉字 拼音
------ -----------
哈尔滨 HEB
*/
declare @table table (id int,name varchar(6))
insert into @table
select 1,'哈尔滨' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'天津' union all
select 5,'广州' union all
select 6,'南京' union all
select 7,'无锡' union all
select 8,'武汉' union all
select 9,'长沙'
SELECT * FROM @table
/*
id name
----------- ------
1 哈尔滨
2 上海
3 北京
4 天津
5 广州
6 南京
7 无锡
8 武汉
9 长沙
*/
DECLARE @char nvarchar(200)
--定义条件
SET @char='w'
SELECT bb.name FROM
(
select *, dbo.fGetPy(name) AS py from @table
)
bb
WHERE substring(py,1,1)=@char/*
name
------
无锡
武汉
*/
但我添加分类的是侯,将它的首字母添加到拼音表中?
这样岂不是很麻烦。我觉得我没太明白你的意思。谢谢你。
就是一个数据表,假设某字段为汉字你在前台文本框输入a
用ajax方法取首字母为a的汉字就可以了
select * from tablename where substring(fGetPy(你要取的汉字字段),1,1)='A'
这个是google suggest经典应用,asp.net ajax里面有这样功能的控件;
我现在用prototype和script.aculo.us框架编写ajax代码,里面有个ajax.autocompleter自动完成控件:<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>ch04</title>
<script src="javascript/prototype.js" type="text/javascript"></script>
<script src="javascript/scriptaculous.js" type="text/javascript"></script>
<style type="text/css">
div.autocomplete
{
position:absolute;
width:250px;
background-color:white;
border:1px solid #888;
margin:0px;
padding:0px;
}
div.autocomplete ul
{
list-style-type:none;
margin:0px;
padding:0px;
}
div.autocomplete ul li.selected
{
background-color: #7DA0BB;
font-weight: bold;
}
div.autocomplete ul li
{
list-style-type:none;
display:block;
margin:0;
padding:2px;
height:16px;
cursor:pointer;
color: #AA3939;
}
</style>
</head>
<body>
<form action="ex15.html">
<div>
<input id="bands" autocomplete="off" size="40" type="text" value="" />
<div class="autocomplete" id="band_list" style="display:none"></div>
</div>
</form> <script type="text/javascript">
// <![CDATA[
new Autocompleter.Local(
"bands",
"band_list", [
"ABBA",
"AC/DC",
"Bay City Rollers",
"Black Sabbath",
"Boston",
"Lynyrd Skynyrd",
"Pink Floyd",
"Queen",
"Ramones",
"The Village People",
"Yes"
], {
tokens: ","
}
); // ]]>
</script></body>
</html>
CREATE table m_table(id int,name varchar(60))
insert into m_table
select 1,'哈尔滨' union all
select 2,'上海' union all
select 3,'北京' union all
select 4,'天津' union all
select 5,'广州' union all
select 6,'南京' union all
select 7,'无锡' union all
select 8,'武汉' union all
select 9,'长沙'
SELECT * FROM m_tableCREATE PROC GetMyDate
(
@char nvarchar(200)
)
as
begin
SELECT bb.name FROM
(
select *, dbo.fGetPy(name) AS py from m_table
)
bb
WHERE py LIKE '%'+@char+'%'
end--执行存储过程
exec GetMyDate 'w'
exec GetMyDate 'wh'