I need to ignore the trailing whitespace when performing a foundset.search, however I can’t use the wildcard ‘%’ since I don’t want it to bring up results containing other characters. For example:
var sSearch = "PEDRO";
var nRows = 0;
if foundset.find()
{
foundset.name = sSearch;
nRows = foundset.Search();
}
I would like to get all occurrences that contain “PEDRO” and any amount of whitespaces to the right.
I can’t go with:
foundset.name = sSearch + "%";
since that would bring up “PEDRO PICAPIEDRA”, “PEDROMONOPALUS”…
I know that a white space is not the same as nothing, thats why the ‘=’ won’t get me what I want. So, how can I meet this requirement?
Thanks for your response. I tried what you suggested but it didn’t work. My table contains the value with trailing white spaces (Its a CHAR field type in Oracle).
I tried all of the following:
var sSearch = "="+"PEDRO "; //One trailing white space.
var sSearch = "="+"PEDRO"; // No trailing space
var sSearch = "PEDRO "; //I also tried filling with the number of spaces to the right so that it matches exactly what is stored in the Database..didnt work either.
//I can only get it to work like this:
var sSearch = "PEDRO%";
//But, like I said, that gets me other values I don't want.
May I ask where you got the suggestion of adding the “=”? I have searched online a lot and haven’t found more info on this.
Any other suggestions? I’m sure I’m not the first to stumble upon this issue.
In plain SQL one would solve this by using the trim function:
SELECT myPK FROM myTable WHERE trim(name) = 'PEDRO'
So you could use this SQL in your search like so:
var sSearch = "PEDRO";
// adjust column/table names accordingly
foundset.loadRecords("SELECT myPK FROM myTable WHERE trim(name)=?",[sSearch]);
var nRows = databaseManager.getFoundsetCount(foundset);
One downside to this is that not every SQL vendor supports the trim function name. Others have ltrim (left) and rtrim (right) for this.
Another way is to add padding to the search string so you pass a string that can exactly match.
var sSearch = "PEDRO",
nRows = 0,
nFieldLength = 10; // lenght of the CHAR column
// Add some padding to the search string (make sure you have enough spaces in the string to add padding)
sSearch = sSearch + utils.stringLeft(" ", nFieldLength - sSearch.length);
if (foundset.find()) {
foundset.name = sSearch;
nRows = foundset.Search();
}
Robert, I would prefer not to use SQL if its possible. I would be very surprised if it can’t be done.
I tried padding with spaces to the right so that it matches exactly what is stored in the Database…didnt work either. To illustrate, lets say “_” is a whitespace and the length of the column NAME is 10. So, using the code you provided I get sSearch = “PEDRO_____”, when I perform a search I get no results, even though the exact value stored in the database IS “PEDRO_____”. To make sure I wasn’t crazy I used foundset.getRecord(nIndex) to get the record I want and when I evaluated foundset.getRecord(nIndex).name == “PEDRO_____” in the Interactive Console, I get true. So, I suspect the search function is doing something strange when comparing values with trailing spaces. Anyone from Servoy can provide their input?
john.allen:
What about using ‘utils.stringTrim()’. That gets rid of preceding or trailing spaces.
Hi john, the thing is I’m doing a foundset.search and the database column has the trailing spaces, not the memory variable I use to compare it with. So I can’t use utils.stringTrim().
I must add, even if the memory variable has trailing spaces as well in order to match exactly what resides in the database, the search function returns 0 rows.