Home Analytics DevShare
The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.

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

thustonthuston Member
edited May 5, 2014 in DevShare
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 = &#91;" ", "(", ")"];
  var parensOpen = 0;
  var parensClose = 0; 
  var doubleQuoteChars = 0;
   
  var str = finds.toLowerCase();
 
  var chs = str.split("");
  for ( i = 0; i&lt; chs.length; i++ ){
    if ( chs&#91;i] == '"' ){
	  if ( doubleQuoteChars % 2 == 0 ){
	    doubleQuoteChars++;
	    token = "";
	  }else{
	    doubleQuoteChars = 0;
	    search&#91;search.length] = token + "";
	    token = "";
	  }	 
    }else if ( doubleQuoteChars % 2 == 0 &amp;&amp; delimiters.indexOf(chs&#91;i]) != -1 ){
	  if (token != ""){
	    search&#91;search.length] = token;
	    token = "";
	  }
	  if (chs&#91;i] == '('){
	    search&#91;search.length] = "(";
	    parensOpen++;
	  }
	  if (chs&#91;i] == ')'){
	    search&#91;search.length] = ")";
	    parensClose++;
	  }   
    }else{
	  token += chs&#91;i];
    } 
  }
  if (doubleQuoteChars % 2 == 1){
    doubleQuoteChars = 0;
    search&#91;search.length] = token + "'";
    token = "";
  }
  if (token != "") {  
    search&#91;search.length] = token; // add the trailing token
  }
  if (parensOpen != parensClose){
    search&#91;search.length] = ")";
    parensClose++;
  } 
 
  for ( t = 0; t &lt; search.length; t++ ){
	    if ( search&#91;t] == "and" || search&#91;t] == "or" || search&#91;t] == "(" || search&#91;t] == ")" ){
		    if ( search&#91;t] == "(" &amp;&amp; t&gt;0 &amp;&amp; !(search&#91;t-1] == "and" || search&#91;t-1] == "or" || search&#91;t-1] == "(" || search&#91;t-1] == ")" ) ){
			    whereClause += " AND ";
		    }
		    whereClause += ( " " + search&#91;t] + " " );
		   
	    }else if ( search&#91;t] == "not" ){
		    t++;
		    whereClause += (  field + " NOT LIKE '%" + search&#91;t] + "%' " );
		   
	    } else {
		    if ( t&gt;0 &amp;&amp;  !(search&#91;t-1] == "and" || search&#91;t-1] == "or" || search&#91;t-1] == "(" ) ) {
		  whereClause += " AND ";
	    }
		    whereClause += (  field + " LIKE '%" + search&#91;t] + "%' " );
	    }
  }
  return whereClause; 
}
Sign In or Register to comment.