JavaScript to take a string containing literals and operators and produce the needed SQL WHERE LIKE
Options
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
- 109 Developer Announcements
- 49 Articles
- 100 General Questions
- 122 IM Services
- 40 OpenText Hackathon
- 31 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 897 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 122 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management