|
'====================================================================. a" i0 C3 W; g6 E- b v
'= Copyright (c) 2005 Eason Chan All Rights Reserved.' P! n' r0 U$ l
'=-------------------------------------------------------------------
/ _" }- K' Q, N" d# Q'= 摘 要:格式化搜索字符函数1 I1 e" n a3 }* ]
'=-------------------------------------------------------------------
, ^" Z: w1 r" L+ ~) P+ x' u2 F$ n! }'= 最后更新:eason007) k4 |8 H! ?- [* v! X5 [
'= 最后日期:2005-01-218 a0 w& i) G/ X( M# H. G9 o# _. ]; O
'====================================================================
1 R4 W; J4 R/ D6 LFunction MakeSQLQuery(QueryField,QueryStr)
7 z( n) x- D, g- E0 ^ Dim TagStart,TagEnd
' a8 W* r: a* C1 F" J Dim TempStr,TempArray7 y1 R8 m. x2 D9 M0 b
Dim FullQueryStr6 F& U! S% ~' m) a$ o! c
Dim i,Way
+ }' V- H! Q+ _4 P: j- H9 z
# D7 [3 }5 b o '先找引号定界符
% }: F3 E3 s5 \' M" n) u Do
9 {% ~/ Q/ f. ~" F0 H% d TagStart=InStr(QueryStr,"\"), F; U+ Y, ?9 k3 T7 s
If TagStart>0 Then
! h) F$ u7 {5 D; O" N% q TagEnd=InStr(TagStart+1,QueryStr,"\")1 p& X0 m3 H! d" R x- g0 Y! R
5 F- }8 }' j0 w/ v
TempStr=Mid(QueryStr,TagStart+1,TagEnd-TagStart-1)
+ `5 k& J( n% |( E; K$ c! P) s& x1 y TempStr=Replace(TempStr," ","#") K$ g! l" x q/ `
0 R1 D: B7 t g
QueryStr=Left(QueryStr,TagStart-1)&TempStr&Right(QueryStr,Len(QueryStr)-TagEnd)
3 M1 i/ r2 @$ [+ s* G2 @) Z! _5 V End If2 _9 M; K/ Y; g. D k
Loop While TagStart>0+ E( d- {& m# H4 W( J6 W0 V
* P( ~8 l$ I' u+ |0 ?5 l '处理or定界符* \8 z% K8 N8 w1 S
QueryStr=Replace(QueryStr,"|"," @")
4 K* P! P8 \, R7 Z '分隔关键字' z! n9 H0 `; ?3 G' F
TempArray=Split(QueryStr," ")
3 N$ x- e3 Z) R, n ( b7 }0 S# ]5 W; i1 L
For i=0 To UBound(TempArray)* h: ]9 q% I3 ~0 ~, o6 O5 S
If Left(TempArray(i),1)="@" Then' d: Z) |" N2 }2 ?4 H
FullQueryStr=FullQueryStr&" Or "&QueryField2 T; y' `6 U* q! C- w6 H
TempArray(i)=Right(TempArray(i),Len(TempArray(i))-1)
# c/ O( S2 _$ }- T Else
2 q! R( V/ ^4 q% l! @/ b FullQueryStr=FullQueryStr&" And "&QueryField
6 R" l: E0 t" |# b End If
, e, u: o: T! X7 o, I2 ]5 r
- |- D$ V# p/ r% T# q" ^' e If Left(TempArray(i),1)="-" Then
8 }" a7 \7 }) d( @, H FullQueryStr=FullQueryStr&" Not "0 L0 P. X9 U/ p
TempArray(i)=Right(TempArray(i),Len(TempArray(i))-1)
- \+ {4 S8 r2 j8 Y End If
! z! G$ x4 u A6 v; Q- W
: R6 F2 h9 ~% A1 M, I FullQueryStr=FullQueryStr&" Like '%"&TempArray(i)&"%'". u1 z* I* r; N4 p4 J$ o4 t
' o5 e% I2 B& j, w
FullQueryStr=Replace(FullQueryStr,"%$","")6 G% r3 ^) P0 @& m D, N. u
FullQueryStr=Replace(FullQueryStr,"$%","")
* X& ~" |3 z7 p( c/ ] FullQueryStr=Replace(FullQueryStr,"#"," ")0 ~! Y! F) Y5 O; n
Next" c/ s" R6 c! X L
/ a" T( o6 e- T MakeSQLQuery=FullQueryStr
) R; R- [! {! yEnd Function7 ]8 K# m' Q9 P& G) c. }& `% z
- Q7 z$ ]: a+ `: k _! P e. {5 v) ^# F( @
说明:: |+ L8 P0 K* ^% l0 s9 D; I+ Y* f
用于搜索时,用户可进行复杂的查找,当前1。0版本只支持单字段的搜索,有心人士可自行修改为支持多字段,但请保留我的版权信息。
0 j }1 D, X( m: K如有疏忽之处,还请原谅!- k( J8 u' |! d' L6 @; ^
9 e+ I0 L: E# y1 y& R示例:
! x$ E0 |! B' m1、 空格连接=and,如 你好 我要=%你好% and %我要%
8 Q# Q2 ?- a+ C1 Z, o: N; |2、 避免内容包含字符=-,如 你好 -我要=%你好% and not like %我要%
; H. Y9 K+ D. \1 w3 i, X3、 |=or,如 你好|我要=%你好% or %我要% 3 R$ G$ u' ]! v9 { P, H" |
4、 词组搜索用双引号包含,如 \i love this game\=%i love this game%,而非=i and love and this and game
5 @( W5 b% T8 F8 c& S% m5、 $为定界符,如 $你好=以 你好 开头的字符,你好$=以 你好 结尾的字符 0 h7 ]. a3 V( e/ W
( f4 v; d& e ^$ r组合查询
- d/ I) a5 S/ J# p W* h如 \i love this game\|-你好=%i love this game% and not like %你好%
6 O: B( i; W. W8 O/ |1 O! m+ H: \! A如 我要$|-$你好=%我要 or not like 你好%
) R& ]" `1 Y/ a如 $\i love this game\ $你好$=i love this game% and like 你好
1 ~3 y* Z5 y/ ?. B
( w0 _! a/ R' }, P/ a/ R/ d9 j D* u调用:
; l, ~7 l3 h2 `4 T2 G7 p: O7 b0 o5 I n4 z
Dim SQL,WSQL9 c/ \ t8 O( D
Dim Keyword,Field( u+ ^( Z2 V2 ~; n. e
; L! c$ D0 k9 W7 ]' O+ Z) C'get keyword& a3 d1 u# Q1 ~( v8 J C8 C, X, W
Keyword=Request("keyword")
& q4 Q9 T% m1 ?Field=Request("field"). V' ]! x0 {6 d/ ~- P1 w# q- `
5 d, b% V2 x9 i) Q5 ?WSQL=MakeSQLQuery(Field,Keyword)" W: D$ F2 ~7 [( X0 s( Q8 h
' o# j4 ^! @9 A' n) X' ?) X0 z/ \
SQL="SELECT * FROM table1 WHERE 1=1 "&WSQL |
|