JavaScript to take a string containing literals and operators and produce the needed SQL WHERE LIKE

thuston

The Javascript code takes the database column name and the search parameter strings and generates the SQL equivalent.<br />
See the jsfiddle example. It supports quoted strings, (), and operators OR, AND, NOT.<br />
A space between literals is treated as an implied AND.<br />
EX.

("Bob Jones" and not Robert) and Sara Smith

<br />
produces

( dbCol LIKE '%bob jones%' and dbCol NOT LIKE '%robert%' ) and dbCol LIKE 
'%sara%' AND dbCol LIKE '%smith%'

<br />
Place the function parseSearch() in initialize along with reportContext.setPersistentGlobalVariable("parseSearch", parseSearch); to make it globally accessible.<br />
<br />
Then use in the dataSet beforeOpen to insert the text into your SQL.

function parseSearch( field, finds ){
  var whereClause = "";
  var search = new Array();
  var token = "";
  var delimiters = [" ", "(", ")"];
  var parensOpen = 0;
  var parensClose = 0; 
  var doubleQuoteChars = 0;
   
  var str = finds.toLowerCase();
 
  var chs = str.split("");
  for ( i = 0; i< chs.length; i++ ){
    if ( chs[i] == '"' ){
	  if ( doubleQuoteChars % 2 == 0 ){
	    doubleQuoteChars++;
	    token = "";
	  }else{
	    doubleQuoteChars = 0;
	    search[search.length] = token + "";
	    token = "";
	  }	 
    }else if ( doubleQuoteChars % 2 == 0 && delimiters.indexOf(chs[i]) != -1 ){
	  if (token != ""){
	    search[search.length] = token;
	    token = "";
	  }
	  if (chs[i] == '('){
	    search[search.length] = "(";
	    parensOpen++;
	  }
	  if (chs[i] == ')'){
	    search[search.length] = ")";
	    parensClose++;
	  }   
    }else{
	  token += chs[i];
    } 
  }
  if (doubleQuoteChars % 2 == 1){
    doubleQuoteChars = 0;
    search[search.length] = token + "'";
    token = "";
  }
  if (token != "") {  
    search[search.length] = token; // add the trailing token
  }
  if (parensOpen != parensClose){
    search[search.length] = ")";
    parensClose++;
  } 
 
  for ( t = 0; t < search.length; t++ ){
	    if ( search[t] == "and" || search[t] == "or" || search[t] == "(" || search[t] == ")" ){
		    if ( search[t] == "(" && t>0 && !(search[t-1] == "and" || search[t-1] == "or" || search[t-1] == "(" || search[t-1] == ")" ) ){
			    whereClause += " AND ";
		    }
		    whereClause += ( " " + search[t] + " " );
		   
	    }else if ( search[t] == "not" ){
		    t++;
		    whereClause += (  field + " NOT LIKE '%" + search[t] + "%' " );
		   
	    } else {
		    if ( t>0 &&  !(search[t-1] == "and" || search[t-1] == "or" || search[t-1] == "(" ) ) {
		  whereClause += " AND ";
	    }
		    whereClause += (  field + " LIKE '%" + search[t] + "%' " );
	    }
  }
  return whereClause; 
}