<% '--- '--- This script written by Eli Fulkerson. '--- http://www.elifulkerson.com for more. '--- '--- '--- Fix from Abe Bender '--- FUNCTION combine_repeating_spaces ( strText ) Dim RegEx Set RegEx = New RegExp RegEx.Pattern = "\s\s*" RegEx.Global = True combine_repeating_spaces = RegEx.Replace(strText, " ") END FUNCTION '--- '--- Internal function, used to support "+keyword -keyword" syntax in addition '--- to "and keyword not keyword" syntax. '--- FUNCTION bq_handle_shorthand(text) text = Replace(text, " +", " and ") text = Replace(text, " -", " not ") bq_handle_shorthand = text END FUNCTION '--- '--- Internal function, used to keep quoted text together when building '--- the query. i.e.... [fish and chips and "chipped ham"] syntax '--- '--- It essentially replaces " " with "~~~~" as long as we aren't within '--- a set of quotes, in which case " " is retained. The string is then '--- split on "~~~~~" with the surviving spaces intact. '--- FUNCTION bq_explode_respect_quotes(line) quote_level = 0 '---keep track if we are in or out of quote-space buffer = "" FOR a = 1 to Len(line) IF Mid(line, a, 1) = """" THEN quote_level = quote_level + 1 IF quote_level = 2 THEN quote_level = 0 END IF ELSE IF Mid(line, a, 1) = " " and quote_level = 0 THEN buffer = buffer & "~~~~" '---Hackish magic key ELSE buffer = buffer & Mid(line, a, 1) END IF END IF NEXT buffer = Replace(buffer, "\\", "") bq_explode_respect_quotes = Split(buffer, "~~~~") END FUNCTION '--- '--- Internal function, used to apply a single keyword against an '--- arbitrary number of fields in the database in the same fashion. '--- '--- Works via replacing whitespace rather than interation '--- FUNCTION bq_make_subquery(fields, word, mode) IF (mode = "not") THEN back = " LIKE '%" & word & "%'))" ELSE back = " LIKE '%" & word & "%')" END IF IF (mode = "not") THEN front = "(NOT (" glue = " LIKE '%" & word & "%' OR " ELSE front = "(" glue = " LIKE '%" & word & "%' OR " END IF text = Replace(fields, " ", glue) text = front & text & back bq_make_subquery = text END FUNCTION '--- '--- Generates the "WHERE" portion of a query, iterating over every keyphrase in the '--- given search string. Is safe to link (e.g. with AND) with the output of repeated '--- calls '--- FUNCTION bq_make_query(fields, text) '--- '--- We can't trust the user to give us a specific case '--- 'fields = Lcase(fields) text = Lcase(text) text = combine_repeating_spaces(text) '--- '--- Support +keyword -keyword '--- text = bq_handle_shorthand(text) '--- '--- Split, but respect quotation '--- wordarray = bq_explode_respect_quotes(text) buffer = "" output = "" '--- '--- work through each word (or "quoted phrase") in the text and build the '--- outer shell of the query, filling the insides via bq_make_subquery '--- '--- "or" is assumed if neither "and" nor "not" is specified '--- FOR i = 0 to Ubound(wordarray) word = wordarray(i) IF word = "and" OR word = "or" OR word = "not" AND i > 0 THEN IF word = "not" THEN '--- '--- $i++ kicks us to the actual keyword that the 'not' is working against, etc '--- i = i + 1 IF i = 1 THEN '---invalid sql syntax to prefix the first check with and/or/not buffer = bq_make_subquery(fields, wordarray(i), "not") ELSE buffer = " AND " & bq_make_subquery(fields, wordarray(i), "not") END IF ELSEIF word = "or" THEN i = i + 1 IF i = 1 THEN buffer = bq_make_subquery(fields, wordarray(i), "") ELSE buffer = " OR " & bq_make_subquery(fields, wordarray(i), "") END IF ELSEIF word = "and" THEN i = i+1 IF i = 1 THEN buffer = bq_make_subquery(fields, wordarray(i), "") ELSE buffer = " AND " & bq_make_subquery(fields, wordarray(i), "") END IF END IF ELSE IF i = 0 THEN '--- 0 instead of 1 here because there was no conditional word to skip and no $i++; buffer = bq_make_subquery(fields, wordarray(i), "") ELSE buffer = " OR " & bq_make_subquery(fields, wordarray(i), "") END IF END IF output = output & buffer NEXT bq_make_query = output END FUNCTION '--- '--- Generates a simple boolean query. Use bq_make_query directly if you need to '--- add further levels of complexity (the output of bq_make_query can be and/or/etc'd '--- with any other output of bq_make_query) '--- '--- '--- return_fields = The fields you want the query to return. '--- "blah1 blah2 blah3" --> "SELECT blah1, blah2, blah3...." '--- '--- tables = The tables you want the query to... query. '--- "t1 t2 t3" --> "FROM t1, t2 '--- FUNCTION bq_simple (return_fields, tables, check_fields, query_text) '--- '--- Convert from space deliniated to comma deliniated for the query '--- return_fields = Replace(return_fields, " ", ", ") tables = Replace(tables," ", ", ") '--- '--- build the query itself '--- query = "SELECT " & return_fields & " FROM " & tables & " WHERE " & bq_make_query(check_fields, query_text) & ";" '--- '--- Uncomment to debug '--- Response.Write(query) bq_simple = query END FUNCTION '--- Example usage to generate boolean query: query = "sam +i +am or ""I do not like them in a house""" bq_simple "hat cat mat", "seuss", "hat cat", query %>