Operators and functions

This section describes functions and operators that are permitted when writing expressions in the connector. Expressions can be used at many points in SQL statements, such as in the ORDER BY, GROUP BY or HAVING clauses of the SELECT statement or in the WHERE clause of the SELECT, DELETE, and UPDATE statement. Expressions can be composed by literal values, column values, functions and operators. In general, there are two types of functions - scalar and aggregate. Scalar functions operate on a single value or list of values, as opposed to aggregate functions, which operate on the data from multiple rows.

See the following categories for further knowledge:


Logical operators and functions

The table below shows logical operators and comparison functions that can be used in the HAVING clause of the SELECT statement or in the WHERE clause of the SELECT, UPDATE and DELETE statement.

The WHERE or HAVING clause is composed by a combination of logical operators and expressions that evaluates either TRUE or FALSE for a row of values. Some functions, such as LEAST() and GREATEST(), return values rather than TRUE or FALSE. However, the return value is still based on comparison operations. The connector performs type conversion as necessary, e.g. string to number or string to date. To convert a value to a specific type for comparison purposes, you can use the CAST() function.

By default, string comparisons are not case sensitive and use invariant culture. The exception is the REGEXP operator which is by default case sensitive. This behaviour can however be specified in the regular expression itself.

NameDescriptionAvailabilty
AND Logical AND ALL
OR Logical OR ALL
NOT Negates comparison ALL
= Equal operator ALL
<>,!= Not equal operator ALL
< Less than operator ALL
<= Less than or equal operator ALL
> Greater than operator ALL
>= Greater than or equal operator ALL
IS NULL Null test value ALL
IS NOT NULL Not null test value ALL
IN() Test if a value is found in a set of values ALL
CONTAINS() Test if an array of values contains any value in a set if values ALL
LIKE Pattern matching ALL
REGEXP Test value against a regular expression ALL
ISNULL() Return TRUE if argument is null ALL
GREATEST() Return the greatest value of all arguments ALL
LEAST() Return the least value of all arguments ALL
COALESCE() Return the first value that is not NULL of all arguments ALL
USERID() Used to compare columns of type User or UserMulti. This server function evaluates to the ID of the authenticated user. WHERE/HAVING
SHOWFIELD() Used on Lookup and User columns to SELECT the display name of referenced list items. ALL
LOOKUP() Used on Lookup and User columns to select both ID and the display name of referenced list items. ALL
CAST() Cast value to specified data type ALL

Logical operators

  • AND expression

    Matches multiple comparisons using logical AND, i.e. all comparisons must evaluate TRUE for complete statement to evaluate TRUE.

    > SELECT * FROM `Customers` WHERE ID = 4 AND Name = 'Steve Watson' AND Created = '2010-05-20'

  • OR expression

    Matches multiple comparisons using logical OR, i.e. at least one comparison must evaluate TRUE for complete statement to evaluate TRUE.

    > SELECT * FROM `Customers` WHERE ID = 4 OR Name = 'Steve Watson' OR Created = '2010-05-20'

  • NOT expression

    Negaties one or more comparisons. Can be used in combinations of other comparison operators and logical operators.

    > SELECT * FROM `Customers` WHERE NOT ID = 4:
    > SELECT * FROM `Customers` WHERE NOT Name IN ('Steve Watsson', 'John Stevesson', 'Wat Johnsson');
    > SELECT * FROM `Customers` WHERE ID = 4 AND Name = 'Steve Watson' AND NOT Created = '2010-05-20'

Comparison operators

  • value1 equal value2

    > SELECT * FROM `Customers` WHERE ID = 4;
    > SELECT * FROM `Customers` WHERE Name = 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created = '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID = 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value1 not equal value2

    > SELECT * FROM `Customers` WHERE ID <> 4;
    > SELECT * FROM `Customers` WHERE Name <> 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created <> '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID <> 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value1 less than value2

    > SELECT * FROM `Customers` WHERE ID < 4;
    > SELECT * FROM `Customers` WHERE Name < 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created < '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID < 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value1 less than or equal value2

    > SELECT * FROM `Customers` WHERE ID <= 4;
    > SELECT * FROM `Customers` WHERE Name <= 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created <= '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID <= 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value1 greater than value2

    > SELECT * FROM `Customers` WHERE ID > 4;
    > SELECT * FROM `Customers` WHERE Name > 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created > '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID > 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value1 greater than or equal value2

    > SELECT * FROM `Customers` WHERE ID >= 4;
    > SELECT * FROM `Customers` WHERE Name >= 'Steve Watson';
    > SELECT * FROM `Customers` WHERE Created >= '2010-05-20';
    > SELECT * FROM `Customers` WHERE GUID >= 'e7538ba0-8835-4d7f-9af3-69b04011ea7a';

  • value IS NULL

    Tests whether a value is NULL.

    > SELECT * FROM `Customers` WHERE Name IS NULL;

  • value IS NOT NULL

    Tests whether a value is not NULL.

    > SELECT * FROM `Customers` WHERE Name IS NOT NULL;

  • value IN (value1, value2, ...)

    Tests whether a value is found in a set of values.

    > SELECT * FROM `Customers` WHERE ID IN (1,2,3,4);
    > SELECT * FROM `Customers` WHERE Name IN ('Steve Watsson', 'John Stevesson', 'Wat Johnsson');

  • array CONTAINS (value1, value2, ...)

    Tests whether an array of values contains any value in a set of values. This type is applicable on MultiChoice and LookupMulti columns.

    > SELECT * FROM `Foo` WHERE MultiChoiceColumn CONTAINS ('bar');
    > SELECT * FROM `Foo` WHERE LookupMultiColumn CONTAINS (44, 43);

  • value LIKE string

    Simple pattern matching (starts with, contains, ends with). The '%' character marks the beginning or end of a word and represents any number of any characters.

    > SELECT * FROM `Customers` WHERE Name LIKE '%sson'
    > SELECT * FROM `Customers` WHERE Name LIKE 'Steve%'
    > SELECT * FROM `Customers` WHERE Name LIKE '%John%'

  • value REGEXP string

    Tests whether a value matches a regular expression. By default, this operator is case sensitive. To turn of case sensitivity, apply the (?i) to your match pattern.

    > SELECT * FROM `Customers` WHERE Name REGEXP '^(Steve|John|Wat) (Steve|Wat|John)sson$';

    Matches Steve Watsson, John Stevesson, Wat Johnsson, but not 'steve watsson'

    > SELECT * FROM `Customers` WHERE Name REGEXP '^(?i)((Steve|John|Wat) (Steve|Wat|John)sson)$';

    Matches Steve Watsson, John Stevesson, Wat Johnsson, and also 'steve watsson'

    > SELECT * FROM `Tasks` WHERE Status REGEXP '^(?!Completed)(?!Deferred)';

    Selects all items from a standard tasklist that are not Completed to Deferred

Comparison functions

  • value ISNULL()

    Evaluates true if value is NULL.

    > SELECT * FROM `Customers` WHERE ISNULL(Name);

    Select all items where Name is null

    > SELECT ISNULL(Name) FROM `Customers`;

    Return True for all items where Name is null
  • GREATEST(value1, value2, ..)

    Returns the greatest value of all arguments

    > SELECT GREATEST(sev1, sev2, sev3) FROM `Severity`;

  • LEAST(value1, value2, ..)

    Returns the least value of all arguments

    > SELECT LEAST(sev1, sev2, sev3) FROM `Severity`;

  • COALESCE(value1, value2, ..)

    Returns the first value in set that is not null

    > SELECT COALESCE(Account, -1) FROM `Customers`;

    Returns -1 if Account is null
  • USERID()

    Evaluates to the current authenticated user, i.e. the user authenticated with the connection string. This function is applicable on User and MultiUser columns. The following example selects all rows from the customers list that was created by the user.

    > SELECT * FROM `Customers` WHERE Author = USERID();

  • SHOWFIELD(column)

    Use on Lookup and User columns to select the display name of referenced lookup items instead of the ID.

    > SELECT SHOWFIELD(User) FROM `Employees`;

    > SELECT * FROM `Employees` WHERE SHOWFIELD(User) = 'Bend Softson';

  • LOOKUP(column)

    Use on Lookup and User columns to select both ID and the display name of referenced lookup items instead of only the ID.

    > SELECT LOOKUP(User) FROM `Employees`;

  • CAST(value, string type)

    Cast value to specified type. The possible types are: DATETIME, BOOLEAN, DOUBLE, NUMBER, INTEGER, STRING, TEXT.

    > SELECT CAST(Title, 'INTEGER') FROM `Orders`;


Visa     MasterCard