CFLib.org – Common Function Library Project

smartSearch(searchterm, field)

Last updated September 27, 2008

author

Craig McDonald

Version: 0 | Requires: CF5 | Library: DatabaseLib

Description:
smartSearch improves keyword searching via SQL by generating a string that can be inserted into an SQL query. It supports parsing of full boolean search terms (AND,OR,NOT) as well as bracket matching and searching of multiple database fields. Perfect for solving issues such as searching of firstname and lastname fields in a table where the submitted keyword is one field.

Return Values:
Returns a string.

Example:

<cfset keywords = "Jimbo Jones">
<cfset fieldname = "tbl_firstname,tbl_lastname">

<cfset sqlSearch = smartSearch(keywords, fieldname, "OR")>

<cfquery>
	SELECT * FROM table WHERE #PreserveSingleQuotes(sqlSearch)#
</cfquery>

Parameters:

Name Description Required
searchterm Search string. Yes
field List of fields to search against. Yes

Full UDF Source:

/**
 * Smart boolean searching in SQL queries.
 * 
 * @param searchterm 	 Search string. (Required)
 * @param field 	 List of fields to search against. (Required)
 * @return Returns a string. 
 * @author Craig McDonald (craig@neuralmotion.com.au) 
 * @version 0, September 27, 2008 
 */
function smartSearch(searchterm, field) {
	//init fields
	var fieldcount = 0;
	var currentfield = "";
	var searchstring = "";
	var startBracketCount = 0;
	var endBracketCount = 0;
	var bracketPoint = 0;
	var searchtermflag = "";
	var counter = "";
	var numchars = "";
	var preboolterm = "";
	var searchportion = "";
	var temp = "";
	var thisSearchTerm = "";
	
	arguments.booloperator = "OR";
	
	if(ArrayLen(arguments) GTE 3)
		arguments.booloperator = arguments[3];
		
	//trim leading and trailing spaces from the search term
	arguments.searchterm = trim(arguments.searchterm);
		
	//Count number of brackets for safety - if there is an uneven number
	//remove them all. Otherwise, it is safe to leave them.
	bracketPoint = Find("(", arguments.searchterm);
	while(bracketPoint IS NOT 0) {
		startBracketCount = startBracketCount + 1;
		bracketPoint = Find("(", arguments.searchterm, bracketPoint+1);
	}	
		
	bracketPoint = Find(")", arguments.searchterm);
	while(bracketPoint IS NOT 0) {
		endBracketCount = endBracketCount + 1;
		bracketPoint = Find(")", arguments.searchterm, bracketPoint+1);
	}
		
	if(startBracketCount IS NOT endBracketCount) {
		//Remove the brackets from the searchterm
		arguments.searchterm = Replace(arguments.searchterm, "(", "", "ALL");
		arguments.searchterm = Replace(arguments.searchterm, ")", "", "ALL");
	}
	
	if(arguments.booloperator IS "EXACT") {
		for (fieldcount = 1; fieldcount LTE ListLen(arguments.field); fieldcount = fieldcount + 1) {
			if(fieldcount IS 1)
				searchstring = searchstring & "(";
			else
				searchstring = searchstring & " OR ";
			
			currentfield = ListGetAt(arguments.field, fieldcount);
			searchstring = searchstring & "(" & currentfield & " Like '%" & arguments.searchterm & "%')";
		}
		
		if (Len(searchstring) GT 0)
			searchstring = searchstring & ")";
	}
	else {
		//init vars
		searchtermflag = 1;
		counter = 1;
		numchars = 0;
		prevboolterm = '';
		
		// Loop until there are no keywords left in the searchterm
		while (counter LTE Len(arguments.searchterm)) {
			//If this is the last searchterm, set the portion to the rest of the string
			if(counter IS Len(arguments.searchterm))
				searchportion = Len(arguments.searchterm);
			else //otherwise find the next keyword
			{
				searchportion = Find(" ", Right(arguments.searchterm, Len(arguments.searchterm) - counter));
				//Check if there is a ( opening bracket at the start of the string and if there is a " directly following
				if(Find("(", Mid(arguments.searchterm, counter, searchportion)) IS 1 AND Find('"', Mid(arguments.searchterm, counter, searchportion)) IS 2)
				{
					//Remove the start quote from the beginning
					attributes.searchterm = RemoveChars(arguments.searchterm, counter + 1, 1);
					searchportion = searchportion - 1;

					//There is, so find the end quote.
					searchportion = Find('"', Mid(arguments.searchterm, counter, Len(arguments.searchterm))) - 1;
					
					//Remove the end quote from the position found
					arguments.searchterm = RemoveChars(arguments.searchterm, counter + searchportion, 1);

					//Check if the last character after the " quote is a ) closing bracket. 
					//If it is, extend the searchportion to include it.
					if(Mid(arguments.searchterm, counter + searchportion, 1) IS ")")
						searchportion = searchportion + 1;
				}
				
				//otherwise find if there's just a quote at the start of the keyword
				if(Find('"', Mid(arguments.searchterm, counter, searchportion)) IS 1)
				{
					//There is, so find the end quote.
					counter = counter + 1;
					temp = 1;
					searchportion = Find('"', Mid(arguments.searchterm, counter, Len(arguments.searchterm))) - 1;
					
					//Remove the end quote from the position found
					arguments.searchterm = RemoveChars(arguments.searchterm, counter + searchportion, 1);
									
					//Check if the last character after the " quote is a ) closing bracket. 
					//If it is, extend the searchportion to include it.
					if(Mid(arguments.searchterm, counter + searchportion, 1) IS ")")
						searchportion = searchportion + 1;					
				}
				
				//if there are no keywords left, set the portion to the rest of the string
				if(searchportion IS 0)
					searchportion = Len(arguments.searchterm);
			}
	
			// Check if this portion contains any boolean terms
			if ((Mid(arguments.searchterm, counter, searchportion) IS "OR" OR Mid(arguments.searchterm, counter, searchportion) IS "AND" OR Mid(arguments.searchterm, counter, searchportion) IS "NOT") AND counter IS NOT 1 AND searchportion IS NOT Len(arguments.searchterm)) {
				// Check if the current boolean term is just a NOT by itself (no AND or OR preceding it)
				if ((prevboolterm IS NOT "AND" AND prevboolterm IS NOT "OR") AND Mid(arguments.searchterm, counter, searchportion) IS "NOT") {
					// Append AND and the boolean term to the SQL string
					searchstring = searchstring & " AND " & Mid(arguments.searchterm, counter, searchportion) & " ";
				}
				else {
					// Append this boolean term to the SQL string
					searchstring = searchstring & " " & Mid(arguments.searchterm, counter, searchportion) & " ";
				}
				
				// Set the previous boolean term to the current boolean term
				prevboolterm = Mid(arguments.searchterm, counter, searchportion);
				
				// Set the search term set flag
				searchtermflag = 1;
			}
			else {
				// Loop through each of the fields to search on
				for (fieldcount = 1; fieldcount LTE ListLen(arguments.field); fieldcount = fieldcount + 1) {
					currentfield = ListGetAt(arguments.field, fieldcount);
				
					//if there were no boolean terms pre-existing, add some
					if(searchtermflag LTE 0)
					{
						//if there's more than one field to search on, OR the keyword
						if(fieldcount GT 1)
							searchstring = searchstring & " OR ";
						else //otherwise, AND the keyword (by default), or whatever the booloperator is set to
							searchstring = searchstring & " " & arguments.booloperator & " ";
					}
					
					//if this is the first field to search on, add an opening bracket
					if(fieldcount IS 1)
						searchstring = searchstring & "(";
					
					//Replace all ' single quotes with '' double quotes - safe parsing
					thisSearchTerm = Replace(Mid(arguments.searchterm, counter, searchportion), "'", "''", "ALL");
					
					//init loop vars
					startBrackets = "";
					endBrackets = "";
					
					//Find any brackets at the start of the searchterm
					bracketPoint = Find("(", thisSearchTerm);
					while(bracketPoint IS NOT 0)
					{
						startBrackets = startBrackets & "(";
						bracketPoint = Find("(", thisSearchTerm, bracketPoint+1);
					}

					//Find any brackets at the end of the searchterm					
					bracketPoint = Find(")", thisSearchTerm);
					while(bracketPoint IS NOT 0)
					{
						endBrackets = endBrackets & ")";
						bracketPoint = Find(")", thisSearchTerm, bracketPoint+1);
					}
					
					//Remove the brackets from the searchterm
					thisSearchTerm = Replace(thisSearchTerm, "(", "", "ALL");
					thisSearchTerm = Replace(thisSearchTerm, ")", "", "ALL");
					
					//append this keyword to the SQL string
					searchstring = searchstring & startBrackets & "(" & currentfield & " LIKE '%" & thisSearchTerm & "%')" & endBrackets;
					//set the end of searchterm flag
					searchtermflag = searchtermflag - 1;
					
					//clear the previous boolean term - should be reset for next word to be checked correctly
					//Re: NOT's without AND's
					prevboolterm = "";
				}	
			}
			
			// If there are no search terms left then close the bracket
			if (searchtermflag LTE 0)
				searchstring = searchstring & ")";
		
			// Move to the next search portion
			counter = counter + searchportion + 1;
		}
	}
	
	//Return the SQL string
	return searchstring;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson