Comparison
Ask whether one value is equal to, larger than, smaller than, like, inside or missing from another, and get true or false back.
Syntax
Write a value, an operator and a value.
a = b a != b a <> b a > b a >= b a < b a <= b
a IN [1, 2] a IN (1, 2) a NOT IN [1, 2]
a LIKE "x%" a NOT LIKE "x%"
a CONTAINS "x" a DOES NOT CONTAIN "x" a STARTS WITH "x" a ENDS WITH "x"
a BETWEEN 1 AND 5 a NOT BETWEEN 1 AND 5
a RANGE(1-5, 8-9) a NOT IN RANGE(1-5)
a IS NULL a IS NOT NULL a IS BLANK a IS NOT BLANKParameters and arguments
leftA record's field, a column, a variable, a function call, or a literal number or text, such as
Required: Yesitem.qtyrightA literal, a field, a computation such as
Required: Yes, except for IS NULL and IS BLANKqty * 2, a bracketed list of values, or another value
Supported operators
A condition can use any of these operators. Each row shows a condition and what it keeps.
| Operator | Example | Meaning |
|---|---|---|
= | financialStatus = "paid" | Keeps only the orders that have been paid; refunded and unpaid orders are left out |
IS | financialStatus IS "paid" | The same test as =, read as a sentence: keeps the paid orders |
!= | channel != "pos" | Keeps the orders that did not come through the point of sale |
<> | state <> "NSW" | Keeps the orders shipped anywhere except New South Wales |
IS NOT | country IS NOT "AU" | Keeps the orders shipped outside Australia |
> | total > 500 | Keeps only the large orders, worth more than 500; an order worth exactly 500 is left out |
IS GREATER THAN | total IS GREATER THAN 500 | The same test as >: keeps the orders worth more than 500 |
>= | total >= 100 | Keeps orders worth 100 or more, the ones a free shipping rule would apply to; smaller orders are left out |
IS GREATER THAN OR EQUAL | total IS GREATER THAN OR EQUAL 100 | The same test as >=: keeps the orders worth 100 or more |
< | shipping < 15 | Keeps the orders whose shipping charge was under 15 |
IS LESS THAN | shipping IS LESS THAN 15 | The same test as <: keeps the orders with a shipping charge under 15 |
<= | discount <= 0 | Keeps the orders that carried no discount |
IS LESS THAN OR EQUAL | discount IS LESS THAN OR EQUAL 0 | The same test as <=: keeps the orders that carried no discount |
LIKE | slug LIKE "%pricing%" | Keeps entries whose slug has pricing in it. The pattern is taken as written: % stands for any run of characters, _ for one, and letter case counts |
NOT LIKE | slug NOT LIKE "%draft%" | Keeps entries whose slug never mentions draft |
CONTAINS | slug CONTAINS "gift" | Keeps entries whose slug mentions gift, in any letter case |
DOES NOT CONTAIN | slug DOES NOT CONTAIN "test" | Keeps entries whose slug never mentions test, so test pages stay out |
STARTS WITH | id STARTS WITH "cal" | Keeps the entries whose id begins with cal, the calendar links |
ENDS WITH | id ENDS WITH "Google" | Keeps the entries whose id ends with Google |
IN [ … ] IN ( … ) | contentType IN ["navLink", "page"] | Keeps entries that are navigation links or pages |
NOT IN [ … ] | contentType NOT IN ["navLink", "page"] | Keeps every kind of entry except navigation links and pages |
BETWEEN … AND … | total BETWEEN 50 AND 500 | Keeps orders worth from 50 up to 500, both ends included |
NOT BETWEEN … AND … | shipping NOT BETWEEN 0 AND 5 | Keeps orders whose shipping charge fell outside 0 to 5, which leaves out free and nearly free shipping |
RANGE(a-b, c-d) | discount RANGE(1-10, 25-50) | Keeps orders with a small discount, 1 to 10, or a deep one, 25 to 50, and leaves out those in between |
NOT IN RANGE(a-b) | tax NOT IN RANGE(0-1) | Keeps orders that paid more than a token amount of tax, leaving out those with none or almost none |
IS NULL | publishedAt IS NULL | Keeps entries that have never been published |
IS NOT NULL | publishedAt IS NOT NULL | Keeps entries that have been published at some time |
IS BLANK | slug IS BLANK | Keeps entries that have no slug, none at all or an empty one. The same test as IS NULL |
IS NOT BLANK | slug IS NOT BLANK | Keeps entries whose slug has text in it. The same test as IS NOT NULL |
MATCHES | title MATCHES "family" | Keeps the assets whose title is about family, found by text search and scored so the closest title ranks first |
MATCHES ANY | title MATCHES ANY ["family", "sunset"] | Keeps the assets about family, about sunsets, or about both |
MATCHES ALL | title MATCHES ALL ["family", "sunset"] | Keeps only the assets about family and sunsets together |
SIMILARITY | title SIMILARITY "family at sunset" | Keeps the assets whose whole title reads much like the phrase |
FUZZY | title FUZZY "sunst" | Keeps the assets whose title holds a word close to sunst, so a misspelling still finds them |
Every comparison answers true or false, so it works the same in a FILTER (which keeps the rows where it is true), in IF and as a field in RETURN. LIKE and NOT LIKE take their pattern as written and letter case counts; CONTAINS, DOES NOT CONTAIN, STARTS WITH and ENDS WITH find their text as itself and ignore letter case. Each answers the same in a FILTER as in a RETURN.
== is not an operator; write =.
Examples
Each comparison operator, and the true or false it returns.
Return type and behaviour
A comparison returns true or false. In a FILTER it keeps the rows for which it is true.
Numbers compare as numbers, so 10 > 9. Text compares character by character and is case sensitive, so "Widget" = "widget" is false and "10" > "9" is false. A date written as text compares in date order, because the year comes first. A number and a text are never equal: 3 = "3" is false.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 | The operator is not one HTQL knows (==), or the left side is a computation | Use an operator from the list, and put a single value on the left |
HTQL-2001 | A column in a FILTER does not exist | Check the column's name |
HTQL-1011 | The left side is not a field or a literal | Compare a record's field |
Placement and context
A comparison is a value in a RETURN, the first argument of IF, and a condition in a FILTER, a HAVING or an AND, OR or NOT. The left side is a single value: a computation is written on the right, as in 5 < qty * 2, not on the left.
Limitations
- Not supported: a computation on the left, such as
item.qty * 2 > 5. Write5 < item.qty * 2, or hold the computation in a variable. - Not supported:
==. Write=.
Best practices
- Quote text values and leave numbers bare.
- Put the value to test on the left and the bound on the right.
- Compare dates as text in the form
2026-01-15. - Use IN or RANGE instead of a chain of ORs.
- Make the rule a named true or false field in your RETURN, such as
"isOverdue".