主页 > 知识库 > asp 多字段模糊搜索的函数

asp 多字段模糊搜索的函数

热门标签:科大讯飞语音识别系统 团购网站 Mysql连接数设置 阿里云 电子围栏 银行业务 服务器配置 Linux服务器

比较简单直接的sql语句

Recordset1.Source = "SELECT * FROM 表 WHERE 字段 LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' or 字段2 like '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' ORDER BY id DESC"
Recordset1.Source = "SELECT * FROM 表 WHERE 字段 LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' and 字段2 = 2 ORDER BY id DESC"

下面是一些补充

函数名:keyword_sousuo
作 用:生成sql查询条件
参 数:table_field ------ 表的字段名(之间用逗号分开)
keyword ------ 搜索关键词(之间用空格分开)
返 回:sql查询条件

 核心代码

'================================================== 
'函数名:keyword_sousuo 
'作 用:生成sql查询条件 
'参 数:table_field ------ 表的字段名(之间用逗号分开) 
' keyword ------ 搜索关键词(之间用空格分开) 
'返 回:sql查询条件 
'================================================== 
function keyword_sousuo(byval table_field,byval keyword) 
 dim str01,str02,keywords,table_fields,i,j 
   
 table_fields=split(trim(table_field),",") 
 keywords=split(trim(keyword),",") 
  
 if table_field>"" then 
  str01="("table_fields(0)" like '%"keyword"%'" 
  for j=0 to ubound(table_fields) 
  str01=str01" or "table_fields(j)" like '%"keyword"%'" 
  next 
  str01=str01")" 
 else 
  response.Write("script>alert('参数错误(不能为空)!')/script>") 
  response.End() 
 end if 

 '全角--》半角 空格 
 'keyword=replace(keyword," "," ") 
 'while InStr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
 for i=0 to ubound(keywords) 
  str02=str02"("table_fields(0)" like '%"keywords(i)"%'" 
  for j=1 to ubound(table_fields) 
  str02=str02" or "table_fields(j)" like '%"keywords(i)"%'" 
  next 
  str02=str02")" 
 next 
 str02="("replace(str02,")(",")and(")")" 
 keyword_sousuo="("  str01  "or"  str02  ")" 
 else 
 keyword_sousuo=str01 
 end if 
end function 
'================================================== 
'函数名:keyword_tag 
'作 用:将字符串里的关键词标记为红色 
'参 数:str ------ 字符串 
' keyword ------ 标记关键词(之间用空格分开) 
'返 回:字符串(html格式) 
'================================================== 
function keyword_tag(byval str,byval keyword) 
 dim keywords,str01,str02,i 
  '全角--》半角 空格 
 'keyword=replace(keyword," "," ") 
 'while InStr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 str01=replace(str,keyword,"font color=""#ff0000"">"keyword"/font>") 
  keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
  str02=str 
 for i=0 to ubound(keywords) 
 str02=replace(str02,keywords(i),"font color=""#ff0000"">"keywords(i)"/font>") 
 next 
  keyword_tag=str02 
 else 
 keyword_tag=str01 
 end if 
end function 
以下是一个例子 http://www.zhouhaihe.com/blog/uploads/200604/recordread.asp 

%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> 
% 
 option explicit 
 dim conn,connstr,database 
 connstr="DBQ="+server.mappath("record.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};" 
 set conn=server.CreateObject("adodb.connection") 
 conn.open connstr 

dim n,rs,page,keyword,keywords,RecordCount,pageCount,pos,i,sql_where 
n=50 
page=Request("page") 
keyword=trim(request("keyword")) 
sql_where=keyword_sousuo("title,cz,name,time",keyword) 
set rs=server.CreateObject("adodb.recordset") 
rs.open "select * from record where "sql_where" order by id desc",conn,1,2 
if page=0 then 
 page=1 
 end if 
RecordCount = 0 
do while not rs.Eof 
 RecordCount = RecordCount +1 
 rs.MoveNext 
loop 
if not RecordCount=0 then 
 rs.MoveFirst 
end if 
pageCount=RecordCount/n 
pageCount=int(pageCount) 
if (RecordCount mod n)>0 then 
 PageCount=PageCount +1 
end if 
%> 
style type="text/css"> 
!-- 
.STYLE3 {color: #666666} 
--> 
/style> 
table width="100%" border="0" cellspacing="0" cellpadding="0"> 
 form id="form1" name="form1" method="post" action="?">tr>td align="center">input name="keyword" type="text" id="keyword" value="%=keyword%>" size="30" /> 
 nbsp; input type="submit" name="Submit" value="搜索" /> 
 span class="STYLE3">(关键词之间用空格分开) /span>/td> 
 
 /tr>/form> 
 tr> 
 td height="5">/td> 
/table> 

table width="600" border="1" align="center" cellpadding="4"> 
 tr align="center"> 
 td width="131">标题/td> 
 td width="131">动作/td> 
 td width="131">用户名/td> 
 td width="132">时间/td> 
 /tr> 
%pos=0 
do while pos(page-1)*n 
pos=pos+1 
rs.moveNext 
loop 
i=0 
while not rs.eof and in 
i=i+1%> 
 tr align="center"> 
 td>%=keyword_tag(rs("title"),keyword)%>/td> 
 td>%=keyword_tag(rs("cz"),keyword)%>/td> 
 td>%=keyword_tag(rs("name"),keyword)%>/td> 
 td>%=keyword_tag(rs("time"),keyword)%>/td> 
 /tr> 
 
 %rs.movenext 
 wend 
 rs.close 
 set rs=nothing 
 conn.close 
 set conn=nothing 
 
'================================================== 
'函数名:keyword_sousuo 
'作 用:生成sql查询条件 
'参 数:table_field ------ 表的字段名(之间用逗号分开) 
' keyword ------ 搜索关键词(之间用空格分开) 
'返 回:sql查询条件 
'================================================== 
function keyword_sousuo(byval table_field,byval keyword) 
 dim str01,str02,keywords,table_fields,i,j 
   
 table_fields=split(trim(table_field),",") 
 keywords=split(trim(keyword),",") 
  
 if table_field>"" then 
  str01="("table_fields(0)" like '%"keyword"%'" 
  for j=0 to ubound(table_fields) 
  str01=str01" or "table_fields(j)" like '%"keyword"%'" 
  next 
  str01=str01")" 
 else 
  response.Write("script>alert('参数错误(不能为空)!')/script>") 
  response.End() 
 end if 

 '全角--》半角 空格 
 keyword=replace(keyword," "," ") 
 ' while InStr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
 for i=0 to ubound(keywords) 
  str02=str02"("table_fields(0)" like '%"keywords(i)"%'" 
  for j=1 to ubound(table_fields) 
  str02=str02" or "table_fields(j)" like '%"keywords(i)"%'" 
  next 
  str02=str02")" 
 next 
 str02="("replace(str02,")(",")and(")")" 
 keyword_sousuo="("  str01  "or"  str02  ")" 
 else 
 keyword_sousuo=str01 
 end if 
end function 
'================================================== 
'函数名:keyword_tag 
'作 用:将字符串里的关键词标记为红色 
'参 数:str ------ 字符串 
' keyword ------ 标记关键词(之间用空格分开) 
'返 回:字符串(html格式) 
'================================================== 
function keyword_tag(byval str,byval keyword) 
 dim keywords,str01,str02,i 
  '全角--》半角 空格 
 keyword=replace(keyword," "," ") 
 'while InStr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 str01=replace(str,keyword,"font color=""#ff0000"">"keyword"/font>") 
  keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
  str02=str 
 for i=0 to ubound(keywords) 
 str02=replace(str02,keywords(i),"font color=""#ff0000"">"keywords(i)"/font>") 
 next 
  keyword_tag=str02 
 else 
 keyword_tag=str01 
 end if 
end function 
dim y 
%> tr align="center"> 
 td colspan="4"> %=RecordCount%>个nbsp; font color="#FF0000">%=page%>/font>/%=pageCount%>页 
 nbsp;%=n%>个/页 转到: 
 SELECT name="select" onchange=window.open(this.options[selectedIndex].value,'_self') > 
 %y=1 
     do while ypagecount+1%> 
 OPTION value=?page=%=y%>keyword=%=server.URLEncode(keyword)%> %if cstr(page)=cstr(y) then response.Write(" selected") end if%> >第%=y%>页/OPTION> 
 %y=y+1 
 loop%> 
 /SELECT>/td> 
 /tr> 
/table>

文章就介绍到这了,希望大家以后多多支持脚本之家。

您可能感兴趣的文章:
  • jquery zTree异步加载、模糊搜索简单实例分享
  • jquery ztree实现模糊搜索功能
  • 模拟一个类似百度google的模糊搜索下拉列表
  • c#反射表达式树模糊搜索示例
  • mysql 模糊搜索的方法介绍
  • 用java等语言仿360首页拼音输入全模糊搜索和自动换肤

标签:江苏 广元 萍乡 大理 蚌埠 枣庄 衢州 衡水

巨人网络通讯声明:本文标题《asp 多字段模糊搜索的函数》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266