asp多字段模糊搜索的函数

比较简单直接的sql语句

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

下面是一些补充

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

 核心代码

"==================================================
"函数名：keyword_sousuo
"作用：生成sql查询条件
"参数：table_field------表的字段名（之间用逗号分开）
"keyword------搜索关键词（之间用空格分开）
"返回：sql查询条件
"==================================================
functionkeyword_sousuo(byvaltable_field,byvalkeyword)
dimstr01,str02,keywords,table_fields,i,j

table_fields=split(trim(table_field),",")
keywords=split(trim(keyword),",")

iftable_field<>""then
str01="("&table_fields(0)&"like"%"&keyword&"%""
forj=0toubound(table_fields)
str01=str01&"or"&table_fields(j)&"like"%"&keyword&"%""
next
str01=str01&")"
else
response.Write("<script>alert("参数错误（不能为空）！")</script>")
response.End()
endif

"全角－－》半角　空格
"keyword=replace(keyword,"　","")
"whileInStr(keyword,"")>0
"keyword=replace(keyword,"","")
"wend
keywords=split(keyword,"")
ifubound(keywords)>0then
fori=0toubound(keywords)
str02=str02&"("&table_fields(0)&"like"%"&keywords(i)&"%""
forj=1toubound(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
endif
endfunction
"==================================================
"函数名：keyword_tag
"作用：将字符串里的关键词标记为红色
"参数：str------字符串
"keyword------标记关键词（之间用空格分开）
"返回：字符串（html格式）
"==================================================
functionkeyword_tag(byvalstr,byvalkeyword)
dimkeywords,str01,str02,i
"全角－－》半角　空格
"keyword=replace(keyword,"　","")
"whileInStr(keyword,"")>0
"keyword=replace(keyword,"","")
"wend
str01=replace(str,keyword,"<fontcolor=""#ff0000"">"&keyword&"</font>")
keywords=split(keyword,"")
ifubound(keywords)>0then
str02=str
fori=0toubound(keywords)
str02=replace(str02,keywords(i),"<fontcolor=""#ff0000"">"&keywords(i)&"</font>")
next
keyword_tag=str02
else
keyword_tag=str01
endif
endfunction
以下是一个例子http://www.zhouhaihe.com/blog/uploads/200604/recordread.asp

<%@LANGUAGE="VBSCRIPT"CODEPAGE="936"%>
<%
optionexplicit
dimconn,connstr,database
connstr="DBQ="+server.mappath("record.mdb")+";DefaultDir=;DRIVER={MicrosoftAccessDriver(*.mdb)};"
setconn=server.CreateObject("adodb.connection")
conn.openconnstr

dimn,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)
setrs=server.CreateObject("adodb.recordset")
rs.open"select*fromrecordwhere"&sql_where&"orderbyiddesc",conn,1,2
ifpage=0then
page=1
endif
RecordCount=0
dowhilenotrs.Eof
RecordCount=RecordCount+1
rs.MoveNext
loop
ifnotRecordCount=0then
rs.MoveFirst
endif
pageCount=RecordCount/n
pageCount=int(pageCount)
if(RecordCountmodn)>0then
PageCount=PageCount+1
endif
%>
<styletype="text/css">
<!--
.STYLE3{color:#666666}
-->
</style>
<tablewidth="100%"border="0"cellspacing="0"cellpadding="0">
<formid="form1"name="form1"method="post"action="?"><tr><tdalign="center"><inputname="keyword"type="text"id="keyword"value="<%=keyword%>"size="30"/>
 <inputtype="submit"name="Submit"value="搜索"/>
<spanclass="STYLE3">(关键词之间用空格分开)</span></td>

</tr></form>
<tr>
<tdheight="5"></td>
</table>

<tablewidth="600"border="1"align="center"cellpadding="4">
<tralign="center">
<tdwidth="131">标题</td>
<tdwidth="131">动作</td>
<tdwidth="131">用户名</td>
<tdwidth="132">时间</td>
</tr>
<%pos=0
dowhilepos<(page-1)*n
pos=pos+1
rs.moveNext
loop
i=0
whilenotrs.eofandi<n
i=i+1%>
<tralign="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
setrs=nothing
conn.close
setconn=nothing

"==================================================
"函数名：keyword_sousuo
"作用：生成sql查询条件
"参数：table_field------表的字段名（之间用逗号分开）
"keyword------搜索关键词（之间用空格分开）
"返回：sql查询条件
"==================================================
functionkeyword_sousuo(byvaltable_field,byvalkeyword)
dimstr01,str02,keywords,table_fields,i,j

table_fields=split(trim(table_field),",")
keywords=split(trim(keyword),",")

iftable_field<>""then
str01="("&table_fields(0)&"like"%"&keyword&"%""
forj=0toubound(table_fields)
str01=str01&"or"&table_fields(j)&"like"%"&keyword&"%""
next
str01=str01&")"
else
response.Write("<script>alert("参数错误（不能为空）！")</script>")
response.End()
endif

"全角－－》半角　空格
keyword=replace(keyword,"　","")
"whileInStr(keyword,"")>0
"keyword=replace(keyword,"","")
"wend
keywords=split(keyword,"")
ifubound(keywords)>0then
fori=0toubound(keywords)
str02=str02&"("&table_fields(0)&"like"%"&keywords(i)&"%""
forj=1toubound(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
endif
endfunction
"==================================================
"函数名：keyword_tag
"作用：将字符串里的关键词标记为红色
"参数：str------字符串
"keyword------标记关键词（之间用空格分开）
"返回：字符串（html格式）
"==================================================
functionkeyword_tag(byvalstr,byvalkeyword)
dimkeywords,str01,str02,i
"全角－－》半角　空格
keyword=replace(keyword,"　","")
"whileInStr(keyword,"")>0
"keyword=replace(keyword,"","")
"wend
str01=replace(str,keyword,"<fontcolor=""#ff0000"">"&keyword&"</font>")
keywords=split(keyword,"")
ifubound(keywords)>0then
str02=str
fori=0toubound(keywords)
str02=replace(str02,keywords(i),"<fontcolor=""#ff0000"">"&keywords(i)&"</font>")
next
keyword_tag=str02
else
keyword_tag=str01
endif
endfunction
dimy
%><tralign="center">
<tdcolspan="4"><%=RecordCount%>个 <fontcolor="#FF0000"><%=page%></font>/<%=pageCount%>页
 <%=n%>个/页转到：
<SELECTname="select"onchange=window.open(this.options[selectedIndex].value,"_self")>
<%y=1
dowhiley<pagecount+1%>
<OPTIONvalue=?page=<%=y%>&keyword=<%=server.URLEncode(keyword)%><%ifcstr(page)=cstr(y)thenresponse.Write("selected")endif%>>第<%=y%>页</OPTION>
<%y=y+1
loop%>
</SELECT></td>
</tr>
</table>