This topic provides selected notes and discussion on operators that are built into the Manifold query engine. The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation. See the list in the query builder tab of the Command Window for an authoritative list of operators, commands, functions, and constants
See the SQL Constants and Literals topic for some useful constants.
 <value> 
Negation 
<value> ^ <value> 
Exponentiation 
<value> % <value> 
Modulo. Divides the first value by the second value and returns the remainder. 
<value> MOD <value> 
Modulo. Divides the first value by the second value and returns the remainder. 4 MOD 3 is 1. 22 MOD 5 is 2. 
<value> DIV <value> 
Integer division. Divides the first value by the second value and returns the integer part of the result. 3 DIV 4 is zero. 7 DIV 5 is 1. 22 DIV 5 is 4. 
<value> / <value> 
Division 
<value> * <value> 
Multiplication 
<value>  <value> 
Subtraction 
<value> + <value> 
Addition for numbers, concatenation of strings. 
<value> & <value> 
Concatenation of strings. 
BITNOT <value> 
Bitwise NOT: Result is opposite of the corresponding bit in the argument. 
<value> BITAND <value> 
Bitwise AND: Result is 1 if corresponding bits in both arguments are 1. 
<value> BITOR <value> 
Bitwise OR: Result is 1 unless corresponding bits in both arguments are 0. 
<value> BITXOR <value> 
Bitwise XOR: Result is 1 if corresponding bits in both arguments are different. Result is 0 if corresponding bits in both arguments are the same. 
<value> LIKE <value> 
Matches first value to a string pattern that may contain wild cards such as '%' (matches any number of characters, including zero characters) and '_' (a single character). See the LIKE Operator topic for examples.
SELECT [NAME] FROM [Mexico Table] WHERE [NAME] LIKE 'Dur%';
Returns Durango.
SELECT [NAME] FROM [Mexico Table] WHERE [NAME] LIKE '%an%';
Returns Guanajuato, Michoacan de Ocampo, Yucatan, Quintana Roo, Durango and San Luis Potosi.

<value> <> <value> 
Not equal to 
<value> = <value> 
Equal to 
<value> >= <value> 
Greater than or equal to 
<value> > <value> 
Greater than 
<value> <= <value> 
Less than or equal to 
<value> < <value> 
Less than 
<value> BETWEEN <value> AND <value> 
Returns True (1) if the first value is between the second (lower) and third (higher) values. For sensible results, the second value should be lower than the third, with an implied syntax of
<value> BETWEEN <lowerbound> AND <upperbound>
The function returns False (0) if the second value is greater than the third value, no matter what the first value. That is an annoying way to enforce the assumption that the second value should be lower than the third, but since virtually all other databases do that for BETWEEN, Manifold does that as well.
? 5 BETWEEN 4 AND 7
Returns true.
? 5 BETWEEN 7 AND 4
Returns false.
? 9 BETWEEN 4 AND 7
Returns false. 
<value> IN (<query>) 
Returns True (1) if the value occurs in the results of the query.
? 'Durango' IN (SELECT [NAME] FROM [Mexico Table])
Returns 1.
? 'Vermont' IN (SELECT [NAME] FROM [Mexico Table])
Returns 0. 
<value> IN (<value>, ...) 
Returns True (1) if the first value occurs in the list of values.
? 'Tom' IN ('Tom', 'Dick', 'Harry')
Returns 1.
? 'John' IN ('Tom', 'Dick', 'Harry')
Returns 0. 
<value> IS NULL 
Returns True (1) if the value is NULL.
? (3 DIV 0) IS NULL
Returns 1.
? (3 DIV 2) IS NULL
Returns 0. 
NOT <value> 
Returns True (1) if the value is boolean False (0)
? NOT (7 < 5)
It is False that 7 is less than 5, so the above returns 1.
? NOT (3 < 5)
It is True that 3 is less than 5, so the above returns 0. 
<value> AND <value> 
Returns True (1) if both values are True. The classic AND logic operator. 
<value> OR <value> 
Returns True (1) if either value is True. The classic OR logic operator. 
<value> XOR <value> 
Returns True (1) if one and only one value is True. Another way to say the same thing: Returns True (1) if the values are different, that is, one is True and one is False. Returns False (0) if both values are True or both values are False. The classic XOR logic operator. 
CAST (<value> AS <type>) 
Converts the data type of the value into the specified type. 
CASTV (<value> AS <type>) 
Takes a vector or tile value and converts the data type of all contained values to the specified type.
CASTV ([vectoroffloat64] AS INT32)
Produces a vector of INT32 values.
CASTV ([tileofuint8x3] AS FLOAT32)
Produces a tile of FLOAT32X3. The number of channels does not change. 
CASE WHEN <condition> THEN <value> ... ELSE <value> END 
Provides IfThenElse logic for queries. When the condition evaluates to True (1) the THEN value is returned. The ELSE part is optional, and if provided when the condition does not evaluate to True (1) the ELSE value is returned.
? CASE WHEN (1 > 0) THEN 5 ELSE 9 END
Returns 5, since the condition of one being greater than zero is always true.
? CASE WHEN (0 > 1) THEN 5 ELSE 9 END
Returns 9, since the condition of zero being greater than one is never true. 
CASE <comparedvalue> WHEN <value> THEN <value> ... ELSE <value> END 
Similar to CASE WHEN <condition> THEN <value> ... ELSE <value> END except that it allows a series of WHEN <value> THEN <value> pairs to test against the compared value.
Suppose we would like to set the number of THREADS to be used to a larger number with more CPUs but not so large as to use all CPUs, so we leave some CPUs free for other processes. The fragment below returns a value based on the number of CPUs reported:
? CASE SystemCpuCount() WHEN 8 THEN 5 WHEN 4 THEN 3 ELSE 1 END
The compared value above is the number of CPUs returned by the SystemCpuCount function. When the number of CPUs reported is 8 then the expression evaluates to 5, when the number of CPUs is 4 the expression evaluates to 3, and otherwise the expression evaluates to 1. The expression above evaluates to 5 for a typical Core i7 machine with hyperthreading enabled.
The above expression could be used as the argument to the THREADS command:
THREADS CASE SystemCpuCount() WHEN 8 THEN 5 WHEN 4 THEN 3 ELSE 1 END
(A useful example written by SQL master Tim Baigent.) 
EXISTS <table> 
Returns True (1) if the argument contains at least one record. 
Vector numeric values, also known as composite numeric values, are pairs or triples or quads of numeric values of types XXXx2, XXXx3, XXXx4 respectively, where XXX can be any of the supported numeric data types. for example, float32. We refer to vector values as x2, x3, x4, or, taken together, xN vector values. Vector values are very useful for representing points in 2D, 3D and 4D spaces, coordinates of rectangles, values in various color spaces like BGR, BGRA when adding an alpha channel to RGB color space, and so on. Comparison operators for vectors compare the component parts of the vector in order.
Example: For the numeric type float64 the xN types available are float64, float64x2, float64x3, and float64x4. float64x3 is a data type consisting of a triplet of numbers each of which is a float64.
(<value>, ...) <> (<value>, ...) 
Not equal to 
(<value>, ...) = (<value>, ...) 
Equal to 
(<value>, ...) >= (<value>, ...) 
Greater than or equal to 
(<value>, ...) > (<value>, ...) 
Greater than 
(<value>, ...) <= (<value>, ...) 
Less than or equal to 
(<value>, ...) < (<value>, ...) 
Less than 
(<value>, ...) BETWEEN (<value>, ...) AND (<value>) 
Returns True (1) if the first value is between the second and third values. 
(<value>, ...) IN (<query>) 
Returns True (1) if the vector value occurs in the results of the query. 
CASTV (<value> AS <type>) 
Takes a vector or tile value and converts the data type of all contained values to the specified type.
CASTV ([vectoroffloat64] AS INT32)
Produces a vector of int32 values.
CASTV ([tileofuint8x3] AS FLOAT32)
Produces a tile of float32x3 values. The number of channels does not change. 
New operators  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation. See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.
Operators for UUID and Binary Values  How selected operators and functions support Universally Unique Identifier (UUID) and binary (VARBINARY) values:
Comparison operators and BETWEEN AND operators for UUID values  The components of UUIDs are compared in order similar to how Windows makes such comparisons.
Comparison operators and BETWEEN AND operators for VARBINARY values  Comparison operators work byte by byte, similar to how they operate on strings.
Division by zero  returns NULL.
Always False, when not True  Operators that return True (1) when the specified condition is satisfied will always return False (0) otherwise.
All types of values  Operators and functions generally support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.
Everything Math  For a handy reference to anything in mathematics, see the Wolfram MathWorld site. Thank you, Wolfram!
Command Window  Query Builder