SemQL operators

Operators in SemQL modify, combine, or compare expressions.

Operators in SemQL are used to:

  • Combine expressions to create new expressions (arithmetic or character operators).

  • Evaluate expressions to return a boolean value. Such operators are used to create conditions.

Arithmetic operators

The following table lists the arithmetic operators available in SemQL.

Operator Description

+

Addition

-

Subtraction

*

Multiplication

/

Division

Character operators

The double pipe (||) is used for string concatenation.

In SQL Server, the || operator is automatically rewritten to the database + operator. Using + also works but is not recommended. Note that concatenation and most operators in SQL Server do not do implicit type conversion, so concatenating two attributes with incompatible types will raise no issue for the SemQL language but will fail during execution.

Comparison operators

The following table lists the comparison operators available in SemQL.

Operator Description

=

Equality

!=, <>

Inequality

>, >=

Greater than, greater than or equal

<, <=

Smaller than, smaller than or equal

IN (value_1, …​, value_n)

Compares a value with each value in the list and returns true if one value matches.

BETWEEN value_1 and value_2

Greater than or equal to value_1 and less than or equal to value_2

LIKE pattern

True if the value matches the pattern. Within the pattern, the character % matches any string of zero or more characters except null. The character _ matches any single character

REGEXP_LIKE(expression, pattern[, parameter])

Returns true if the expression matches the pattern.

REGEXP_LIKE has a boolean result and is considered a condition and not a function.

The parameter value may contain one or more of the following options:

  • i: case-insensitive match (supported for Oracle and PostgreSQL only).

  • c: case-sensitive match (supported for Oracle only).

  • n: allows the period (.) to match the `newline character' instead of `any character' (supported for Oracle only).

  • m: treats the source string as a multiple-line input (supported for Oracle only).

This function behaves differently depending on the data location database technology:

  • For Oracle:

    • This function rewrites to the Oracle REGEXP_LIKE function, which supports POSIX regular expressions in the pattern.

  • For PostgreSQL, the Pattern Matching operator, which supports POSIX regular expressions, is used:

    • REGEXP_LIKE(expression, pattern, 'i') rewrites to expression ~* pattern.

    • REGEXP_LIKE(expression, pattern) rewrites to expression ~ pattern.

  • For SQL Server:

    • Only the REGEXP_LIKE(expression, pattern) syntax is supported. It rewrites to expression LIKE pattern. This operator supports wildcard patterns. See LIKE for more details.

IS [NOT] NULL

Tests for nulls

any child_entity_role have ( <condition_on_child_entity> )

Condition that returns true if any of the child records—​in a one-to-many relationship—​meets the given condition. For more information, see Use attributes of related entities.

all <child_entity_role> have ( <condition_on_child_entity> )

Condition that returns true if all of the child records—​in a one-to-many relationship—​meet the given condition. For more information, see Use attributes of related entities.

SEM_EQUAL_OR_ONE_NULL(value_1, value_2)

Condition that returns true if the two values are equal or one of them is null.

SEM_EQUAL_OR_BOTH_NULL(value_1, value_2)

Condition that returns true if the two values are equal or both are null.

Logical operators

The following table lists the logical operators available in SemQL.

Operator Description

AND

Returns true if both conditions are true.

OR

Returns true if one condition of the other is true.

NOT

Returns true if the following condition is false.