Operators Overview

OmniFabric supports a comprehensive set of operators for performing various operations on data. This page provides an overview of all available operators organized by category.

Operator Categories

Arithmetic Operators

Perform mathematical calculations on numeric values.

Operator Description Link
+ Addition Addition
- Subtraction Minus
- Unary minus (negation) Unary Minus
* Multiplication Multiplication
/ Division Division
DIV Integer division DIV
%, MOD Modulo (remainder) MOD

Comparison Operators

Compare values and return boolean results.

Operator Description Link
= Equal to Equal
<>, != Not equal to Not Equal
< Less than Less Than
<= Less than or equal Less Than or Equal
> Greater than Greater Than
>= Greater than or equal Greater Than or Equal
BETWEEN ... AND ... Value within range BETWEEN
NOT BETWEEN ... AND ... Value outside range NOT BETWEEN
IN Value in list IN
NOT IN Value not in list NOT IN
LIKE Pattern matching LIKE
NOT LIKE Pattern not matching NOT LIKE
ILIKE Case-insensitive pattern matching ILIKE
IS Test for specific values IS
IS NOT Test for not specific values IS NOT
IS NULL Test for NULL values IS NULL
IS NOT NULL Test for non-NULL values IS NOT NULL

Logical Operators

Combine or modify boolean expressions.

Operator Description Link
AND Logical AND AND
OR Logical OR OR
NOT Logical NOT NOT
XOR Logical exclusive OR XOR

Bitwise Operators

Perform bit-level operations on integer values.

Operator Description Link
& Bitwise AND Bitwise AND
\| Bitwise OR Bitwise OR
^ Bitwise XOR Bitwise XOR
~ Bitwise inversion (NOT) Bitwise Inversion
<< Left shift Left Shift
>> Right shift Right Shift

Assignment Operators

Assign values to variables or columns.

Operator Description Link
= Assignment Equal

Cast and Conversion Operators

Convert data between different types.

Operator/Function Description Link
CAST Convert data type CAST
CONVERT Convert data type with format CONVERT
BINARY Convert to binary string BINARY
ENCODE Encode data ENCODE
DECODE Decode data DECODE
SERIAL Serialize data SERIAL
SERIAL_FULL Full serialization SERIAL_FULL

Flow Control Functions

Control program flow based on conditions.

Function Description Link
CASE WHEN Conditional expressions CASE WHEN
IF Simple conditional IF
IFNULL Handle NULL values IFNULL
NULLIF Return NULL if equal NULLIF

Special Comparison Functions

Function Description Link
COALESCE Return first non-NULL value COALESCE
ISNULL Test for NULL ISNULL

| STRCMP | String comparison | STRCMP | | INTERVAL | Time interval operations | INTERVAL |

Other Operators

Operator Description Link
INTERVAL Time and date intervals INTERVAL

Operator Precedence

Understanding operator precedence is crucial for writing correct expressions. See the Operator Precedence guide for detailed information about the order of operations.

Usage Guidelines

Arithmetic Operations

  • Use parentheses to control evaluation order
  • Be aware of integer vs. decimal division
  • Handle division by zero appropriately

Comparison Operations

  • Use appropriate operators for data types
  • Consider NULL handling in comparisons
  • Use LIKE for pattern matching with wildcards

Logical Operations

  • Use parentheses to group complex conditions
  • Understand short-circuit evaluation behavior
  • Consider NULL values in logical expressions

Bitwise Operations

  • Primarily used with integer data types
  • Useful for flag operations and bit manipulation
  • Consider signed vs. unsigned integer behavior

Type Conversions

  • Use explicit CAST when needed
  • Be aware of implicit type conversions
  • Handle conversion errors appropriately

Performance Considerations

  1. Index Usage: Some operators can prevent index usage
  2. Type Matching: Avoid unnecessary type conversions
  3. Complex Expressions: Break down complex expressions for readability
  4. NULL Handling: Consider performance impact of NULL checks

For detailed syntax and examples of each operator, click on the individual links above.