Advanced SQL Query Builder

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
Upload