ich hatte einen geistesblitz ;o)) ich glaube mit umlauten gibt es probleme, teste am besten selber mal aus
SQLStatement = SQLStatement & " WHERE ((([pdf-archiv].erscheinungsdatum)<=Date())) AND " & buildquery(Feld,suchbegriff) in feld stehen die felder in einem array welche felder durchsucht werden sollen, suchbegriff erkl�rt sich von selber. trinkst du diebels? ich brauch 125 kronkorken vom jubil�ums-diebels -----Urspr�ngliche Nachricht----- Von: Thomas Beckert [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 22. November 2002 11:41 An: AspGerman Kaffeehaus Betreff: [aspdecoffeehouse] Re: AW: suche in datenbank super... ist ja aufwendiger, als ich gedacht habe. selbst geschrieben ? ;) �hm... nur eine ganz dumme frage- was �bergebe ich denn als argumente... da sind ja zwei... ??? ... ----- Original Message ----- From: "Steppel" <[EMAIL PROTECTED]> To: "AspGerman Kaffeehaus" <[EMAIL PROTECTED]> Sent: Friday, November 22, 2002 11:31 AM Subject: [aspdecoffeehouse] AW: suche in datenbank > 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 > | [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
