'**************************************
    ' Name: Advanced SQL Query Builder
    ' Description:This code lets visitors to
    '     your site perform complex queries. Users
    '     may choose if specific words (or phrases
    '     ) must or must not match - or if they ar
    '     e optional (default).
    ' By: Lewis Moten
    '
    ' Inputs:asFieldsAry - An array of field
    '     names in database to search.
    asKeywords - The actual query that the user types To query the database.
    Keyword Search Parameters
    To find fields that may have a word in them
    	OR roger
    	| roger
    	roger
    To find fields that must match a word
    	AND roger
    	+ roger
    	& roger
    To find fields that must Not match a word
    	Not roger
    	- roger
    Also use phrases
    	+"rogers dog" -cat
    	+(rogers dog)
    '
    ' Returns:Returns just the SQL arguments
    '     within a group that are to be places aft
    '     er the WHERE Clause.
    '
    ' Assumes:It is assumed that the user kn
    '     ows how to build an array of field names
    '     and understand that syntax of SQL querie
    '     s along with how to connect to databases
    '     . This procedure has only been tested wi
    '     th SQL Servers and Access databases.
    '
    ' Side Effects:This function uses the Re
    '     gExp object that was introduced in vbScr
    '     ipt 5.0 that came out with Internet Expl
    '     orer 5.0. The vbScript can be installed 
    '     without installing Internet Explorer by 
    '     going to the subdirectory "Scripting" on
    '     the microsoft site.
    '
    'This code is copyrighted and has    ' limited warranties.Please see http://w
    '     ww.Planet-Source-Code.com/xq/ASP/txtCode
    '     Id.6297/lngWId.4/qx/vb/scripts/ShowCode.
    '     htm    'for details.    '**************************************
    
    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 = "((AND|[+&])\s*[\(\[\{""].*[\)\]\}""])|((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 = "(((NOT|[-])\s*)?[\(\[\{""].*[\)\]\}""])|(((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 = "(((OR|[|])\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 = "^(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 = "^(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 = "^(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