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;
}