sicher :-)
<%
Function buildQuery(ByRef asFieldAry, ByVal asKeyWords)
Dim loRegExp ' Regular Expression Object (requires vbScript 5.0)
Dim loRequiredWords ' Words that MUST match within a search
Dim loUnwantedWords ' Words that MUST NOT match within a search
Dim loOptionalWords ' Words that AT LEAST ONE must match
Dim lsSQL ' Arguments of SQL query that is returned (WHERE __Arguments___)
Dim lnIndex ' Index of an array
Dim lsKeyword ' Keyword or Phrase being worked with
' An error may occur within your script
' Even if you do not call this function
' If you do not have vbScript 5.0 installed on your server
' because of the next line.
' Create regular Expression
Set loRegExp = New RegExp
' Match more then once
loRegExp.Global = True
' Every letter is created equal (uppercase-lowercase = same)
loRegExp.IgnoreCase = True
' pull out keywords and phrases that MUST match within a search
loRegExp.Pattern =
"((UND|AND|[+&])\s*[\(\[\{""].*[\)\]\}""])|((UND|AND\s|[+&])\s*\b[-\w����']+
\b)"
Set loRequiredWords = loRegExp.Execute(asKeywords)
asKeywords = loRegExp.Replace(asKeywords, "")
' pull out keywords and phrases that MUST NOT match within a search
loRegExp.Pattern =
"(((NICHT|NOT|[-])\s*)?[\(\[\{""].*[\)\]\}""])|(((NICHT|NOT\s+|[-])\s*)\b[-\
w����']+\b)"
Set loUnwantedWords = loRegExp.Execute(asKeywords)
asKeywords = loRegExp.Replace(asKeywords, "")
' pull out keywords and phrases that must have AT LEAST ONE match within a
search
loRegExp.Pattern =
"(((ODER|OR|[|])\s*)?[\(\[\{""].*[\)\]\}""])|(((ODER\s+|OR\s+|[|])\s*)?\b[-\
w����']+\b)"
Set loOptionalWords = loRegExp.Execute(asKeywords)
asKeywords = loRegExp.Replace(asKeywords, "")
' If at least 1 required word was found
If Not loRequiredWords.Count = 0 Then
' REQUIRED
' Open a new group
lsSQL = lsSQL & "("
' loop through each keyword/phrase
For lnIndex = 0 To loRequiredWords.Count - 1
' Pull the keyword out
lsKeyword = loRequiredWords.Item(lnIndex).Value
' Strip boolean language
loRegExp.Pattern = "^(UND|AND|[+&])\s*"
lsKeyword = loRegExp.Replace(lsKeyword, "")
loRegExp.Pattern = "[()""\[\]{}]"
lsKeyword = loRegExp.Replace(lsKeyword, "")
' Double Quote Keyword
lsKeyword = Replace(lsKeyword, "'", "''")
' If we are not working with the first keyword
If Not lnIndex = 0 Then
' append logic before the keyword
lsSQL = lsSQL & " AND "
End If ' Not lnIndex = 0
' Append SQL to search for the keyword within all searchable fields
lsSQL = lsSQL & "(" & Join(asFieldAry, " LIKE '%" & lsKeyword & "%' OR ") &
" LIKE '%" & lsKeyword & "%')"
Next ' lnIndex
' Close the group
lsSQL = lsSQL & ")"
End If ' Not loRequiredWords.Count = 0
' If at least 1 optional word was found
If Not loOptionalWords.Count = 0 Then
' OPTIONAL
' If the SQL query is not yet defined
If lsSQL = "" Then
' Open a new group
lsSQL = "("
' Else SQL query has content
Else
' Append logic before the group
lsSQL = lsSQL & " AND ("
End If ' lsSQL = ""
' loop through each keyword/phrase
For lnIndex = 0 To loOptionalWords.Count - 1
' Pull the keyword out
lsKeyword = loOptionalWords.Item(lnIndex).Value
' Strip Boolean Language
loRegExp.Pattern = "^(ODER|OR|[|])\s*"
lsKeyword = loRegExp.Replace(lsKeyword, "")
loRegExp.Pattern = "[()""\[\]{}]"
lsKeyword = loRegExp.Replace(lsKeyword, "")
' Double Quote the keyword
lsKeyword = Replace(lsKeyword, "'", "''")
' If we are not working with the first keyword
If Not lnIndex = 0 Then
' Append Logic before the keyword search
lsSQL = lsSQL & " OR "
End If ' Not lnIndex = 0
' Append SQL to search for the keyword within all searchable fields
lsSQL = lsSQL & "(" & Join(asFieldAry, " LIKE '%" & lsKeyword & "%' OR ") &
" LIKE '%" & lsKeyword & "%')"
Next ' lnIndex
' Close the group
lsSQL = lsSQL & ")"
End If ' Not loOptionalWords.Count = 0
' If at least 1 Unwanted word was found
If Not loUnwantedWords.Count = 0 Then
' UNWANTED
' If the SQL query is not yet defined
If lsSQL = "" Then
' Open a new group
lsSQL = "("
' Else SQL query has content
Else
' Append logic before the group
lsSQL = lsSQL & " AND NOT ("
End If ' lsSQL = ""
' loop through each keyword/phrase
For lnIndex = 0 To loUnwantedWords.Count - 1
' Pull the keyword out
lsKeyword = loUnWantedWords.Item(lnIndex).Value
' Strip Boolean Language
loRegExp.Pattern = "^(NICHT|NOT|[-])\s*"
lsKeyword = loRegExp.Replace(lsKeyword, "")
loRegExp.Pattern = "[()""\[\]{}]"
lsKeyword = loRegExp.Replace(lsKeyword, "")
' Double Quote the keyword
lsKeyword = Replace(lsKeyword, "'", "''")
' If we are not working with the first keyword
If Not lnIndex = 0 Then
' Append Logic before the keyword search
lsSQL = lsSQL & " OR "
End If ' Not lnIndex = 0
' Append SQL to search for the keyword within all searchable fields
lsSQL = lsSQL & "(" & Join(asFieldAry, " LIKE '%" & lsKeyword & "%' OR ") &
" LIKE '%" & lsKeyword & "%')"
Next ' lnIndex
' Close the group
lsSQL = lsSQL & ")"
End If ' Not loUnwantedWords.Count = 0
' If arguments were created
If Not lsSQL = "" Then
' Encapsilate Arguments as a group
' in case other aguments are to be appended
lsSQL = "(" & lsSQL & ")"
End If ' Not lsSQL = ""
' Return the results
BuildQuery = lsSQL
End Function ' BuildQuery
%>
-----Urspr�ngliche Nachricht-----
Von: Thomas Beckert [mailto:[EMAIL PROTECTED]]
Gesendet: Freitag, 22. November 2002 11:29
An: AspGerman Kaffeehaus
Betreff: [aspdecoffeehouse] suche in datenbank
hi liste -
ich will eine suchfunktion f�r unsere datenbank machen.
dabei m�chte ich ber�cksichtigen, dass die benutzer auch nach mehreren
begriffen suchen.
�blich sind ja verbindungen wie + oder & aber auch einfach leerzeichen.
manche setzen ja auch die worte in "" ...
dass ich mit dem like befehl suchen muss, ist mir klar.
aber wie bekomme ich die suchbegriffe in die richtige form bzw. wie
formatiere ich den sql-string so, dass eben genausoviele likes wie
suchbegriffe sind und mit and oder or verkn�pft sind ...
vielleicht hat ja jemand schon eine sch�ne funktion .. :)
gruss-
tom.
| [aspdecoffeehouse] als [EMAIL PROTECTED] subscribed |
http://www.aspgerman.com/archiv/aspdecoffeehouse/ = Listenarchiv | Sie
k�nnen sich unter folgender URL an- und abmelden: |
http://www.aspgerman.com/aspgerman/listen/anmelden/aspdecoffeehouse.asp
| [aspdecoffeehouse] als [email protected] subscribed
| http://www.aspgerman.com/archiv/aspdecoffeehouse/ = Listenarchiv
| Sie k�nnen sich unter folgender URL an- und abmelden:
| http://www.aspgerman.com/aspgerman/listen/anmelden/aspdecoffeehouse.asp
