Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Operator

Description

Applicable Data Types

Example

||

OR: Used to implement a logical OR for two or more search conditions in the same data provider

Any

Code Block
 // Cities of London or Berlin
city = 'Berlin||London';

|

Format: Used to separate a value and an implied format.

Date

Code Block
 // exactly 01/01/2001 (00:00:00 implied)
orderdate = '01/01/2001|MM/dd/yyyy';

!

Not: Used to implement a logical NOT for a search condition.

Any

Code Block
// Anything but Berlin
city = '!Berlin';

#

Sensitivity Modifier: Implies a case-insensitive search for text columns. Implies a match on entire day for date columns.

Text, Date

Code Block
// i.e. Los Angeles, lOS aNGeLES
city = '#los angeles';

// any time on 01/01/2001
orderdate = '#01/01/2001|MM/dd/yyyy';

^

Is Null: Matches records where a column is null.

Any

Code Block
// All null contact names, not including empty strings
contactname = '^';

^=

Is Null/Empty/Zero: Matches records where a column is null, empty string value or zero numeric value

Text, Numeric

Code Block
// All freights which are null or 0
freight = '^=';

<

Less than: Matches records where the column is less than the operand

Any

Code Block
// i.e. 50, 99.99, but not 100, 101
freight = '<100';

<=

Less than or equal to: Matches records where the column is less than or equals the operand

Any

Code Block
// i.e. Atlanta, Baghdad, Berlin, but not Buenos Aires, Cairo
city = '<=Berlin';

>=

Greater than or equal to: Matches records where the column is greater than or equals the operand

Any

Code Block
// Any time on/after 12am new year's day 2001
orderdate = '>=01/01/2001|MM/dd/yyyy';

>

Greater than: Matches records where the column is greater than the operand

Any

Code Block
// i.e. 100.01, 200, but not 99,100
freight = '>100';

...

Between: Matches records where the column is between (inclusive) the left and right operands.

Any

Code Block
// Any time during the year 2001
orderdate = '01/01/2001...01/01/2002|MM/dd/yyyy';

// i.e.
freight = '100...200';

// i.e. London, Lyon, Madrid, Omaha, Portland
city = 'London...Portland';

%

Wild Card String: Matches records based on matching characters and wild cards

Text

Code Block
city = 'New%';    // Starts with: i.e. New York, New Orleans
city = '%Villa%; // Contains: i.e. Villa Nova, La Villa Linda
city = '%s';     // Ends with: i.e. Athens, Los Angeles

_

Wild Card Character: Matches records based on

Text

Code Block
// i.e. Toledo, Torino
city = '%To___o%';

\

Escape Character: Used to escape other string operators

Text

Code Block
// Escape the wild card, i.e. ...50% of Capacity...
notes = '%\%%';

now

Now: Matches records where the condition is right now, including time

Date

Code Block
// exact match on this second
creationdate = 'now';

today

Today: Matches records where the condition is any time today

Date

Code Block
// match on anytime today
orderdate = 'today';

Using find mode from scripting without using special operators or spaces

You can use find mode with non-strings as well. For example, dates, numbers are not interpreted and will be used literally.

Arrays can be used when searching for multiple values, these are also not interpreted.

Code Block
if(foundset.find()) {
    city = ['Berlin', 'Amsterdam'] // city in (?, ?) {'Berlin', 'Amsterdam'}
    companyid = 42; // literal numerical value
    startdate = new Date(99,5,24,11,33,30,0); // literal date value
    foundset.search();   // Execute the query and load the records
}

Note that when you use a string for searching, it will be trimmed (except in case of a CHAR column, which is padded with spaces by the database).

If you want to make sure the argument is not interpreted, us a single-element array:

Code Block
if (foundset.find()) {
    // tag = ' Hello Servoy ';     // would search for trimmed    
    tag = [' Hello Servoy '];     // will search for literal (untrimmed)
    foundset.search();   // select ... from ... where tag = ? {' Hello Servoy '}
}

Find Mode and the User Interface

...