|
'====================================================================
% x) ]4 x" L. h. f0 E'= Copyright (c) 2005 Eason Chan All Rights Reserved.( x6 A+ Z% t' t, T% E0 a# [
'=-------------------------------------------------------------------
. f) J' Q) ^' ^ |* ['= 摘 要:格式化搜索字符函数% @0 x; b) \ U, a
'=-------------------------------------------------------------------0 H) ]' I5 a3 M" H0 J
'= 最后更新:eason007
1 D! k6 v6 U) c/ [) \'= 最后日期:2005-01-21
8 s+ o# ]. j$ f" E) U9 \'====================================================================
4 b& c* X! c& B& ^3 _* |Function MakeSQLQuery(QueryField,QueryStr)
" J$ R! R& F: L# h Dim TagStart,TagEnd
& C% L( ^7 X( n* e% y; j Dim TempStr,TempArray
1 \: u' D L4 L# T- G Dim FullQueryStr
: |3 s) M" ?- Z9 [: G g Dim i,Way
6 `9 O' B. H" k3 | / }; m" d$ _& ~5 N+ p/ b+ u7 @' E
'先找引号定界符
, @% i4 [# f2 C Do
( _" k+ K2 h( R+ i TagStart=InStr(QueryStr,"\")
/ h' J2 e1 Q5 U' ^ If TagStart>0 Then ! m" b# F5 m4 D% C
TagEnd=InStr(TagStart+1,QueryStr,"\")
/ ?; G0 Q( m z 8 k$ |; h! h& m, N- b+ N0 q; R
TempStr=Mid(QueryStr,TagStart+1,TagEnd-TagStart-1)3 ~4 @7 f' W7 m; L8 n+ H0 C
TempStr=Replace(TempStr," ","#")
# o! K* T: k* d& F- d5 e7 S4 q % Y$ v- W' _6 u* R* I1 m9 I/ N, G
QueryStr=Left(QueryStr,TagStart-1)&TempStr&Right(QueryStr,Len(QueryStr)-TagEnd)- w: [; O7 j; K, i, b) N
End If
\9 \- j0 {* U( g2 v. m/ o; P Loop While TagStart>0" t2 \- H4 Y2 v' Y$ _
, {. ~1 o% ^4 P! w6 s0 `0 ^' |
'处理or定界符
( S O& k- O+ M/ c4 ] QueryStr=Replace(QueryStr,"|"," @"). t f; T1 I/ n2 C0 W
'分隔关键字. M$ z$ J9 l1 X' |. ^' J
TempArray=Split(QueryStr," ")
! S) c4 o& U$ H' v + q. u: v' K+ t; o2 S
For i=0 To UBound(TempArray)
& l8 A+ {- P2 W h If Left(TempArray(i),1)="@" Then
. L4 e+ `! I* ] FullQueryStr=FullQueryStr&" Or "&QueryField
9 f- d" v/ W* w$ @5 p2 X TempArray(i)=Right(TempArray(i),Len(TempArray(i))-1)
" q; r. F8 B- O; x) [$ [9 c2 x Else
5 A1 M, q- D# Y4 v! |( m& D FullQueryStr=FullQueryStr&" And "&QueryField& f& x( ~, ?3 G5 P- F0 L# W/ l5 r
End If5 g: [7 c- {# n5 D9 m8 P
; L$ t& M1 I/ `( r- S
If Left(TempArray(i),1)="-" Then ) G$ y* f( w/ G% p0 r0 b
FullQueryStr=FullQueryStr&" Not "
# `% {. R, A, @' U TempArray(i)=Right(TempArray(i),Len(TempArray(i))-1)
1 `0 s" o s9 ~* N End If h) Q6 H* @ Z ], {
7 g2 o0 s+ h( V3 Q1 Z
FullQueryStr=FullQueryStr&" Like '%"&TempArray(i)&"%'"
/ E& @) i9 a4 E7 k4 B/ o. M
# a% X4 Y( d, ? FullQueryStr=Replace(FullQueryStr,"%$","")3 v8 K' ]9 c4 x, }9 E2 c( o, [
FullQueryStr=Replace(FullQueryStr,"$%","")
_1 ]7 A; X& e! {0 S f* y FullQueryStr=Replace(FullQueryStr,"#"," ")3 {8 ~. ^' B' w; ]" V' r
Next: v' }7 ?! n6 e
$ w. @; y X: {; V$ D MakeSQLQuery=FullQueryStr
' q/ y- a. B( j! ]7 t- _End Function
5 W9 F6 o% x% u( x) ~$ q, N$ h4 q- S5 N& Z4 Z/ Q; r
+ q( y) \8 f# e6 I# G7 Q8 ^* k说明:
. s* |2 h- s( M用于搜索时,用户可进行复杂的查找,当前1。0版本只支持单字段的搜索,有心人士可自行修改为支持多字段,但请保留我的版权信息。/ L) K: ~% \' l4 ?1 G
如有疏忽之处,还请原谅!( f; n% v/ R- ^! x
- @5 ^9 ~4 Q" L: C! e: S示例:
+ c1 d+ `% o/ h7 a- a1、 空格连接=and,如 你好 我要=%你好% and %我要%
- ?/ p# i3 I3 C5 Q) S e5 S5 a8 Q2、 避免内容包含字符=-,如 你好 -我要=%你好% and not like %我要% % W% Q4 l4 N! L0 k- O @5 ^
3、 |=or,如 你好|我要=%你好% or %我要% # ~% T3 P) v5 b- b
4、 词组搜索用双引号包含,如 \i love this game\=%i love this game%,而非=i and love and this and game . s3 i* v; H2 ]7 c3 t7 S; \
5、 $为定界符,如 $你好=以 你好 开头的字符,你好$=以 你好 结尾的字符
# \/ W9 Z+ B1 o! p; k3 Y6 s+ b
1 k5 n. [% u& n; k: R( C! x组合查询 " Q! }4 P2 a6 s7 z3 K
如 \i love this game\|-你好=%i love this game% and not like %你好% ( K' t- d1 w- y. Y# d
如 我要$|-$你好=%我要 or not like 你好% 2 \9 [1 E v' G) c" {
如 $\i love this game\ $你好$=i love this game% and like 你好 , T8 p0 e4 j: ^: n9 h r; D
8 _& f# r: @$ j m: N
调用:
. O) r5 Y0 e" I2 n
5 U+ T J) t% n q' N4 dDim SQL,WSQL
0 S v: a! K$ xDim Keyword,Field
* R3 N1 O4 I. h6 e" Z* Z
- S1 T* n7 B" ?2 p! a- y& m'get keyword
" T% l4 {8 Z* c( ZKeyword=Request("keyword")
; v1 L( s5 s$ O% l6 \% |( m3 JField=Request("field")
9 i/ {+ Q" u/ s0 D8 Z
, v4 j; B) _& A! H Y2 LWSQL=MakeSQLQuery(Field,Keyword)
( u4 p$ q) `- B+ h/ n/ P
: T1 H9 ]# x/ y* ?# uSQL="SELECT * FROM table1 WHERE 1=1 "&WSQL |
|