Skip to main content

Operators

ON THIS PAGE

Operators

The sections below describe the SQLite operators you can use to build an expression with the Expression builder. The Expression builder divides the operators into the following categories:

Note:

With the exception of table-level transformations, all operator symbols must be preceded by a space and followed by a space. For example, the expression for concatenating a first and last name should be specified like this:

FIRST_NAME || LAST_NAME

And not like this:

FIRST_NAME||LAST_NAME

Strings

You can use the following string:

||

Name: Concatenate strings.

Examples:

FIRST_NAME || LAST_NAME

PHONE_NUMBER || <Office Only> (adds the string Office Only to the telephone number).

Logical

The following table describes the logical SQLite operators used by the Qlik Replicate Expression Builder.

Logical operators

Operator

Description

!= or <>

Is not equal to

$SALARY!=100000

IS

Is the same as

$HIRE_DATE IS 2014-09-29

IS functions the same as = unless one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false).

IS NOT

Is not the same as

$HIRE_DATE IS NOT 2014-09-29

IS NOT functions the same as != unless one or both of the operands are NULL. In this case, if both operands are NULL, the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS NOT operator evaluates to 1 (true).

IN

The IN operator takes a single scalar operand on the left and a vector operand on the right formed by an explicit list of zero or more scalars or by a single subquery. When the right operand of an IN operator is a subquery, the subquery must have a single result column. When the right operand is an empty set, the result of IN is false regardless of the left operand and even if the left operand is NULL.

SQLite allows the parenthesized list of scalar values on the right-hand side of an IN operator to be an empty list but most other SQL endpoint engines and the SQL92 standard require the list to contain at least one element.

LIKE

The LIKE operator does a pattern matching comparison. The operand to the right of the LIKE operator contains the pattern and the left operand contains the string to match against the pattern. A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the LIKE pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent. (By default SQLite only understands upper/lower case for ASCII characters. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)

LIKE can be preceded by the NOT keyword.

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions.

Example 1:

WHEN $NEWEST = 'Y' THEN '1' ELSE '0' END

Example 2:

case length($month)

when 2 then $year||$month

when 1 then $year||0||$month end

GLOB

The GLOB operator acts in the same way as the LIKE operator but uses the UNIX file globbing syntax for its wildcards. GLOB is case sensitive.

GLOB can be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the function glob(Y,X) and can be modified by overriding that function.

MATCH

The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

REGEXP

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message.

AND

Both operands are true.

$MANAGER_ID AND EMPLOYEE ID >100

OR

Either operand is true.

$MANAGER_ID OR EMPLOYEE ID >100

<<

Bitwise shift left.

x << n

A bitwise shift to the left of x by n bits.

>>

Bitwise shift right.

x >> n

A bitwise shift to the right of x by n bits.

&

Unary and

|

Unary or

<

Is less than.

$SALARY<100000

<=

Is less than or equal to

$SALARY<=100000

>

Is greater than

$SALARY>100000

>=

Is more than or equal to

$SALARY>=100000

= or ==

Is equal to

$SALARY=100000

Mathematical

The following table describes the mathematical SQLite operators used by the Expression Builder.

Mathematical operators
Operator Description

+

Adds two values together.

DEPARTMENT_ID+100 (adds 100 to each ID number). Any column used in an expression with this operator must be a numeric data type.

-

Subtracts a value from another value.

MANAGER_ID-100 (subtracts 100 from each ID number). Any column used in an expression with this operator must be a numeric data type.

%

Uses the remainder of a division expression as the value.

%SALARY/7 (Divides the value of the Salary column by 7 and uses any remainder from the expression as the column value).

/

Divides one value into another.

SALARY/.16 (Divides the value of the Salary column by .16.

Note: If the two values in the division expression are integers (two NUMERIC columns with no digits after the decimal) and the result is a fractional value, the result returned will be 0.

*

SALARY*.16 (Multiplies the value of the Salary column by .16. This could be used to calculate taxes that are subtracted from a salary).