In-flight processing
Expression reference
Every operator, function, cast and literal you can use in row conditions and output column expressions, with examples, types and null handling.
Row conditions and output column expressions use a small, strict subset of SQL. This page lists everything the subset accepts, with examples. Anything else is refused before the pipeline starts, with the codes listed in Expression errors. Size limits are in Validation and limits.
Syntax
- Write each expression in the source database's SQL dialect, for example T-SQL for a SQL Server source or Oracle SQL for an Oracle source.
- An expression is one scalar value. Subqueries,
SELECTclauses, aggregate and window functions are refused. - Refer to columns of the table by their unqualified names, such as
amount, noto.amount. Names match without regard to case. Quote a name the dialect's way when it needs quoting, for example[Order Date]or"Order Date". COLLATEandAT TIME ZONEare refused.
Quoted names and block comments work as in the dialect:
[Order Date] IS NOT NULL AND /* only real orders */ [Is Test] = CAST('false' AS BIT)Literals
| Literal | Examples | Notes |
|---|---|---|
| Number | 42, -7, 19.99 | Exact decimal, up to 38 digits with up to 38 after the point. |
| Text | 'active', 'O''Brien', N'Zürich' | Single quotes. Double a quote inside the text. The N prefix is accepted. |
| Null | NULL |
The example row
The examples on this page are written in T-SQL for a SQL Server table dbo.Orders and show what each expression returns for this row:
| Column | Type | Value |
|---|---|---|
order_id | int | 1001 |
customer_name | nvarchar(100) | Ada Lovelace |
email | nvarchar(255) | Ada@Example.com |
status | varchar(20) | shipped |
note | nvarchar(200) | ' gift ' (two spaces on each side) |
quantity | int | 12 |
unit_price | decimal(10,2) | 9.99 |
discount | decimal(10,2) | NULL |
is_paid | bit | 1 (true) |
ordered_at | datetime2 | 1 May 2024, 13:45:00 |
shipped_at | datetime2 | NULL |
embedding | vector(2) | [3, 4] |
centroid | vector(2) | [4, 3] |
Operators
| Operator | Operands | Result | Notes |
|---|---|---|---|
AND, OR | Boolean | Boolean | Three-valued logic: NULL AND false is false, NULL OR true is true, otherwise NULL propagates. |
NOT | Boolean | Boolean | NOT NULL is NULL. |
=, <>, <, <=, >, >= | Two values of compatible types | Boolean | NULL if either side is NULL. |
IN (…), NOT IN (…) | A value and 1 to 128 literals | Boolean | The list holds literals only. A NULL in the list makes a non-match NULL. |
IS NULL, IS NOT NULL | A value of any type in Types except binary | Boolean | Never NULL. |
+, -, * | Exact numbers | Exact number | Never rounds. NULL if either side is NULL. |
Unary -, + | Exact number | Exact number |
Division and remainder are not supported. In T-SQL, + is numeric only: join text with CONCAT. In dialects that have the concatenation operator ||, it behaves like CONCAT. Write a range as two comparisons, for example amount >= 100 AND amount < 500.
Operator examples
| Expression | Result |
|---|---|
is_paid OR discount > 0 | true (true OR NULL is true) |
discount > 0 AND is_paid | NULL (NULL AND true) |
discount > 0 AND NOT is_paid | false (NULL AND false is false) |
status = 'Shipped' | false: text compares case-sensitively |
customer_name < 'B' | true: text compares by UTF-16 code units |
discount = 0 | NULL |
status IN ('shipped', 'delivered') | true |
status NOT IN ('open', NULL) | NULL: the NULL in the list makes the non-match unknown |
shipped_at IS NOT NULL | false |
quantity * unit_price | 119.88 |
quantity + discount | NULL |
status = 42 | Refused: incompatible-expression-types |
is_paid = 1 | Refused: incompatible-expression-types. Use is_paid alone. |
quantity IN (1, order_id) | Refused: in-literal-required |
In a row condition, a NULL result means the row isn't kept.
Functions
| Function | Arguments | Returns | Notes |
|---|---|---|---|
CASE WHEN c THEN r … [ELSE e] END | Boolean conditions, results of a common type | The common type | A NULL condition counts as false. Without ELSE, the result is NULL. |
CASE x WHEN v THEN r … [ELSE e] END | A value compared with = to each v | The common type | |
COALESCE(a, b, …) | Two or more values of a common type | The first non-null value | |
CAST(x AS type) | See Casts | The target type | |
CONCAT(a, b) | Exactly two text values | Text | NULL if either is NULL, unlike SQL Server's own CONCAT. Nest calls to join more. |
LOWER(x), UPPER(x) | Text | Text | Unicode case mapping, independent of collation. Not allowed on a column in row conditions. |
TRIM(x) | Text | Text | Removes leading and trailing spaces only. TRIM(LEADING …) and TRIM(chars FROM x) are refused. |
PARTIAL_MASK(x, keep_start, keep_end) | Text and two whole-number literals | Text | See Masking. |
HASH_MASK(x, 'alias') | Text or binary, and a text literal | varchar(64) | See Masking. |
REDACT('text') | A text literal | The literal | See Masking. |
VECTOR_DISTANCE('metric', v1, v2) | 'cosine', 'euclidean' or 'dot', and two vector values of the same shape | float | |
VECTOR_NORM(v, 'norm') | A float32 vector, and 'norm1', 'norm2' or 'norminf' | float | |
VECTOR_NORMALIZE(v, 'norm') | A float32 vector, and 'norm1', 'norm2' or 'norminf' | vector |
Every function returns NULL when an argument it reads is NULL, except COALESCE and CASE. LOWER and UPPER on a column, PARTIAL_MASK and HASH_MASK can't be used in row conditions.
Function names are not case-sensitive. Any other function is refused. HASH and KEYED_HASH are refused too; use HASH_MASK. DISTINCT, OVER, WITHIN GROUP, named arguments and schema-qualified function names are refused.
Function examples
| Expression | Result |
|---|---|
CASE WHEN quantity >= 100 THEN 'bulk' WHEN quantity >= 10 THEN 'case' ELSE 'unit' END | case |
CASE status WHEN 'open' THEN 1 WHEN 'shipped' THEN 2 END | 2 |
CASE WHEN discount > 0 THEN 'discounted' ELSE 'full price' END | full price: the NULL condition counts as false |
COALESCE(discount, 0) | 0.00 |
COALESCE(discount, 'none') | Refused: incompatible-expression-types |
CONCAT(CONCAT(customer_name, ' / '), status) | Ada Lovelace / shipped |
CONCAT(status, CAST(discount AS VARCHAR(12))) | NULL |
UPPER(TRIM(note)) | GIFT |
CONCAT(customer_name, ' ', status) | Refused: function-arity |
UPPER(order_id) | Refused: string-type |
PARTIAL_MASK(email, 1, 3) | A***********com |
HASH_MASK(LOWER(email), 'customer') | The same hash as for ada@example.com |
HASH_MASK(order_id, 'customer') | Refused: mask-input-type |
Vector function examples
| Expression | Result |
|---|---|
VECTOR_NORM(embedding, 'norm2') | 5 |
VECTOR_NORMALIZE(embedding, 'norm2') | A vector(2) holding 0.6 and 0.8 |
VECTOR_DISTANCE('euclidean', embedding, centroid) | About 1.414 |
VECTOR_DISTANCE('cosine', embedding, centroid) | About 0.04 |
VECTOR_DISTANCE('dot', embedding, centroid) | -24 (the negative dot product) |
Vector functions work in a table's own output columns only: in a row condition they are refused with processing_source_predicate_unsupported, and under All tables with unsupported-function.
Casts
CAST(x AS type) accepts these target types:
| Target type | Default size | Notes |
|---|---|---|
INT, BIGINT, SMALLINT, TINYINT | ||
DECIMAL(p, s), NUMERIC(p, s) | (38, 0) | p from 1 to 38, s from 0 to p. |
VARCHAR(n), CHAR(n), NVARCHAR(n), NCHAR(n) | 1,048,576 characters | n from 1 to 1,048,576. |
TEXT | 1,048,576 characters | Treated as nvarchar. |
BIT | Boolean. BOOLEAN is accepted too, in dialects that have it. | |
DATETIME, DATETIME2 | Date and time without offset. TIMESTAMP is accepted too, in dialects where it names a date and time type. | |
DATETIMEOFFSET | Date and time with offset. TIMESTAMP WITH TIME ZONE is accepted too, in dialects that have it. |
TRY_CAST, CONVERT, array types, user-defined types and any other type, such as DATE, are refused.
Which conversions are allowed:
| From | To number | To text | To BIT | To date and time |
|---|---|---|---|---|
| Number | Yes | Yes | No | No |
| Text | Yes | Yes | Yes | Yes |
BIT | No | Yes | Yes | No |
| Date and time | No | Yes | No | Same kind only |
uniqueidentifier | No | Yes | No | No |
Casts never round, truncate or guess. A value that doesn't convert exactly stops the pipeline when that row is processed:
| Conversion | Rule | Error |
|---|---|---|
| To an integer type | The value must be whole and fit the type. | integer-overflow-or-fraction, or scalar-evaluation-failed for a whole number outside the range of INT, SMALLINT or TINYINT |
To DECIMAL(p, s) | The value must have at most s decimal places, ignoring trailing zeros, and fit p digits. 1.2300 fits DECIMAL(18,2), 1.2345 doesn't. | numeric-overflow-or-rounding, numeric-overflow |
| Text to number | Plain digits with an optional sign and decimal point, such as -12.50. No exponent, spaces or thousands separators. | invalid-number |
To text of length n | The result must fit n characters. | string-overflow |
Text to BIT | Exactly true or false, in lowercase. | boolean-cast |
Text to DATETIME2 | ISO 8601 round-trip form, such as 2024-05-01T13:45:00.0000000. | datetime-cast |
Text to DATETIMEOFFSET | ISO 8601 round-trip form with an offset, such as 2024-05-01T13:45:00.0000000+02:00. | datetime-offset-cast |
Casting to text writes numbers in plain decimal form, BIT as true or false, dates in the same ISO 8601 round-trip form, and uniqueidentifier values as 36 lowercase characters with hyphens.
Cast examples
| Expression | Result |
|---|---|
CAST(unit_price AS DECIMAL(18,4)) | 9.9900 |
CAST(unit_price AS VARCHAR(10)) | '9.99' |
CAST(is_paid AS VARCHAR(5)) | 'true' |
CAST(ordered_at AS VARCHAR(33)) | '2024-05-01T13:45:00.0000000' |
CAST('2024-05-01T13:45:00.0000000' AS DATETIME2) | 1 May 2024, 13:45:00 |
CAST(NULL AS VARCHAR(10)) | NULL, typed varchar(10) |
CAST(unit_price AS INT) | Stops: integer-overflow-or-fraction, because 9.99 has a fraction |
CAST(status AS INT) | Stops: invalid-number, because 'shipped' isn't a number |
CAST(ordered_at AS DATE) | Refused: unsupported-cast |
"Stops" means the expression is accepted, and the pipeline stops when it processes this row. "Refused" means the rule is refused before the start.
Types
Each column has the type family of its source column:
| Family | Source types (examples) | Can be used in |
|---|---|---|
| Exact number | tinyint, smallint, int, bigint, decimal, numeric, money, Oracle NUMBER | Everything |
| Approximate number | float, real | Comparisons with numbers in output columns, CASE, COALESCE. No arithmetic or casts. |
| Text | char, varchar, nchar, nvarchar and their (max) forms | Everything. Values up to 1,048,576 characters. |
| Boolean | bit | Conditions, comparisons with other Boolean values, casts to text |
| Date and time | date, datetime, datetime2, datetimeoffset, Oracle DATE, TIMESTAMP | Comparisons with the same kind, and casts, in output columns only |
uniqueidentifier | uniqueidentifier | Comparisons in output columns, casts to text |
| Binary | binary, varbinary, Oracle RAW | Pass-through and HASH_MASK only |
Other types, such as time or spatial types, can be kept as plain columns but can't be used in expressions (opaque-expression-type).
Two values are compatible when they are in the same family. Exact numbers mix freely, and an approximate number can be compared with an exact one. Text never converts to a number or a date by itself: quantity = '12' and ordered_at > '2024-01-01' are refused with incompatible-expression-types. Cast text explicitly where you need a date.
Text compares as binary UTF-16, whatever the column's collation: comparisons are case-sensitive and accent-sensitive, and trailing spaces count.
Row conditions accept fewer forms than output columns, because the initial load runs them on the source. See Row filtering: The initial load.
