Important alert: (current site time 9/18/2014 7:20:23 AM EDT)
 

VB icon

Advanced SQL Query Builder

Email
Submitted on: 8/3/2000 10:57:26 PM
By: Lewis E. Moten III  
Level: Intermediate
User Rating: By 9 Users
Compatibility: ASP (Active Server Pages)
Views: 61695
author picture
(About the author)
 
     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 are optional (default).

 
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
' for :Advanced SQL Query Builder
'**************************************
Copyright (C) 1999, Lewis Moten. All rights reserved. Any modifications must be sent to me so that I may review and add them to the procedure as I feel fit.
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
'**************************************
' 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 are optional (default).
' By: Lewis E. Moten III
'
' 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 after the WHERE Clause.
'
' Assumes:It is assumed that the user knows how to build an array of field names and understand that syntax of SQL queries along with how to connect to databases. This procedure has only been tested with SQL Servers and Access databases.
'
' Side Effects:This function uses the RegExp object that was introduced in vbScript 5.0 that came out with Internet Explorer 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://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=6297&lngWId=4'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


Other 102 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

8/4/2000 4:09:15 PMfrihani

You seem to have an excellent grasp on regular expressions. This code demonstrates the power and usefulness of RegExp, hopefully others will find similar uses.
(If this comment was disrespectful, please report it.)

 
8/4/2000 9:52:34 PMLewis Moten

Thanks. I read a good O'Reilly book about them call Mastering Regular Expressions. I still have a few other tricks up my sleeves with them. Most of the time I use them to export data from standard text-based forms on the internet
(If this comment was disrespectful, please report it.)

 
4/18/2001 10:20:28 PMap

hi Lewis,
Can i form where clause , which looks like following , using your code?
1. A>10 and (b > (c or d))

(If this comment was disrespectful, please report it.)

 
8/5/2002 3:22:45 PMVictor Campbell

Re Embedded Boolean Operators Within asKeyWords:
I'm doing development for a fantasy gaming site. If the first word happens to start with 'or' (e.g., 'orc AND miniature'), it treats that first 2 characters as if they were the Boolean operator, searching only for '%c%'. Reversing the words (e.g., 'miniature AND orc') parses correctly.
Might there be a simple fix to assure an adjacent space when a hit is found at position 1?
--Vic
(If this comment was disrespectful, please report it.)

 
8/16/2002 7:40:13 AMLuis

Great job, but there is a problem: if you enter not roger without anything else it returns all records that have roger in it. we want the opposite.
(If this comment was disrespectful, please report it.)

 
7/22/2004 9:15:59 AM

Fantastic job - how do you include phrases in the search? When submitting a phrase enclosed with parenth... It still separates the words.. Thanks!


(If this comment was disrespectful, please report it.)

 
8/2/2006 1:06:56 AMvino

if u don't mind can i hv to full version of the codin.plz send to me.thanks
(If this comment was disrespectful, please report it.)

 
3/12/2007 2:46:52 PMGary Smith

Lewis - is this the latest code? Its now 2007, just checking :)

Thanks!

(If this comment was disrespectful, please report it.)

 
10/12/2007 9:03:35 AMNathan

I had This adv sql qurey script working an an XP web server while testing (4 years ago.I dug it out again to reuse.)and now I have MS server 2003 STD SP 2 and the script no longer works. Could this be the RegExp mentioned in the code. MS script update is only for XP and 2000, Will not install on server 2003. Is this the reasons or could it be something else?
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.