Operators

Is not equal to

$SALARY!=100000

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 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).

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.

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.

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

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.

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.

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.

Both operands are true.

$MANAGER_ID AND EMPLOYEE ID >100

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

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.