JavaScript to take a string containing literals and operators and produce the needed SQL WHERE LIKE
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.
produces
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.
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; }
0
Categories
- All Categories
- 108 Developer Announcements
- 53 Articles
- 106 General Questions
- 144 IM Services
- 43 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 899 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 120 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management