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 BLANK

Parameters and arguments

  • left

    A record's field, a column, a variable, a function call, or a literal number or text, such as item.qty

    Required: Yes
  • right

    A literal, a field, a computation such as qty * 2, a bracketed list of values, or another value

    Required: Yes, except for IS NULL and IS BLANK

Supported operators

A condition can use any of these operators. Each row shows a condition and what it keeps.

OperatorExampleMeaning
=financialStatus = "paid"Keeps only the orders that have been paid; refunded and unpaid orders are left out
ISfinancialStatus 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 NOTcountry IS NOT "AU"Keeps the orders shipped outside Australia
>total > 500Keeps only the large orders, worth more than 500; an order worth exactly 500 is left out
IS GREATER THANtotal IS GREATER THAN 500The same test as >: keeps the orders worth more than 500
>=total >= 100Keeps orders worth 100 or more, the ones a free shipping rule would apply to; smaller orders are left out
IS GREATER THAN OR EQUALtotal IS GREATER THAN OR EQUAL 100The same test as >=: keeps the orders worth 100 or more
<shipping < 15Keeps the orders whose shipping charge was under 15
IS LESS THANshipping IS LESS THAN 15The same test as <: keeps the orders with a shipping charge under 15
<=discount <= 0Keeps the orders that carried no discount
IS LESS THAN OR EQUALdiscount IS LESS THAN OR EQUAL 0The same test as <=: keeps the orders that carried no discount
LIKEslug 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 LIKEslug NOT LIKE "%draft%"Keeps entries whose slug never mentions draft
CONTAINSslug CONTAINS "gift"Keeps entries whose slug mentions gift, in any letter case
DOES NOT CONTAINslug DOES NOT CONTAIN "test"Keeps entries whose slug never mentions test, so test pages stay out
STARTS WITHid STARTS WITH "cal"Keeps the entries whose id begins with cal, the calendar links
ENDS WITHid 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 500Keeps orders worth from 50 up to 500, both ends included
NOT BETWEEN … AND …shipping NOT BETWEEN 0 AND 5Keeps 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 NULLpublishedAt IS NULLKeeps entries that have never been published
IS NOT NULLpublishedAt IS NOT NULLKeeps entries that have been published at some time
IS BLANKslug IS BLANKKeeps entries that have no slug, none at all or an empty one. The same test as IS NULL
IS NOT BLANKslug IS NOT BLANKKeeps entries whose slug has text in it. The same test as IS NOT NULL
MATCHEStitle MATCHES "family"Keeps the assets whose title is about family, found by text search and scored so the closest title ranks first
MATCHES ANYtitle MATCHES ANY ["family", "sunset"]Keeps the assets about family, about sunsets, or about both
MATCHES ALLtitle MATCHES ALL ["family", "sunset"]Keeps only the assets about family and sunsets together
SIMILARITYtitle SIMILARITY "family at sunset"Keeps the assets whose whole title reads much like the phrase
FUZZYtitle 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
ErrorWhat happenedHow to recover
HTQL-1003The operator is not one HTQL knows (==), or the left side is a computationUse an operator from the list, and put a single value on the left
HTQL-2001A column in a FILTER does not existCheck the column's name
HTQL-1011The left side is not a field or a literalCompare 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. Write 5 < 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".