SQL Operators

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 and functions.

Constants

Some useful constants:

 

CR

A string constant for a carriage return character.

CRLF

A string constant consisting of CR plus LF, the end of line sequence in Windows.

LF

A string constant for a line feed character.

WHITESPACE

A string constant for use with StringTrimXxx functions:  contains a space character, a tab character, CR and LF.

FALSE

A boolean.

TRUE

A boolean.

E

A number, 2.718...

Pi

A number, 3.1415...

 

Operators

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

<value> & <value>

Bitwise AND: Result is 1 if corresponding bits in both arguments are 1.

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 a complete list of wildcards as well as 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 and third values.

 

? 5 BETWEEN 7 AND 4

 

Returns 1.

 

? 9 BETWEEN 7 AND 4

 

Returns 0.

<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 ([vector-of-float64] AS INT32)

 

Produces a vector of INT32 values.

 

CASTV ([tile-of-uint8x3] AS FLOAT32)

 

Produces a tile of FLOAT32X3.   The number of channels does not change.

CASE WHEN <condition> THEN <value> ... ELSE <value> END

Provides If-Then-Else 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 <compared-value> 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 <query>

Returns True (1) if the argument contains at least one record.

 

Vector (Tuple) Operators

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 ([vector-of-float64] AS INT32)

 

Produces a vector of int32 values.

 

CASTV ([tile-of-uint8x3] AS FLOAT32)

 

Produces a tile of float32x3 values.   The number of channels does not change.

 

Notes

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:

 

 

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!

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

SQL Statements

 

SQL Functions

 

Temporary Databases

 

EXECUTE