Query operators in CONSTRUCT

The CONSTRUCT instruction supports a specific query syntax, using wildcard characters and comparison operators.

Table 1. CONSTRUCT query operators
Symbol Meaning
value Use value as is to filter
= (without value) Is NULL
> value Greater than value
>= value Greater than or equal to value
< value Less than value
<= value Less than or equal to value
<> value or != value Not equal to value
<> or != (without value) Is not NULL
value1:value2 or value1..value2 Range from value1 to value2
value1|value2 [|...] List of values
Table 2. CONSTRUCT character wildcards
Symbol Meaning
* A sequence of zero to n of any characters
? Any single-character at this position
[c1-c2] A character in the specified range, at this position
[^c1-c2] A character NOT in the specified range, at this position
[c1c2 [...] ] A character in the specified set, at this position
[^c1c2 [...] ] A character NOT in the specified set, at this position

Queries based on character types are case sensitive, because SQL is case sensitive, except if the database server is configured to be case-insensitive.

The * (star) and ? (question mark) wildcards are specific to character string type queries, and will generate a MATCHES expression or a LIKE expression, depending on the type of database used. When entering a * or ?, the pattern can also contain a character range specification with the square brackets notation [a-z] or [xyz]. A caret ( ^. ) as the first character within the square brackets specifies the logical complement of the set, and matches any character that is not listed. For example, the search value [^AB]* specifies all strings beginning with characters other than A or B.

Some syntaxes can produce an "Error in field" dialog error if the feature is supported by the pattern matching operator of the database server. For example, not all db servers support the [a-z] character range specification in the LIKE pattern.

If you want to search for rows with values containing a * star, a ? question mark or a \ backslash, you must escape the wildcard character with a backslash. Specifying a backslash before another character will have no effect.

Table 3. CONSTRUCT input examples with matching and non matching values
QBE input example Matching values Non matching values
100 100 99, 101, NULL
>=100 100, 101, 200 10, 99, NULL
!=100 98, 98, 101, 102 100, NULL
!= 98, 99, 100, 101 NULL
1:100 1, 2 ... 99, 100 0, 101, NULL
aaa:yyy aaa, aab, ab, yy, yyy zaa, NULL
abc abc bc, abcd, Abc, NULL
ABC ABC abc, aBC, NULL
abc* abc, abcd, abcdef bc, ABC, NULL
*bc abc, bc acd, aBC, NULL
?bc abc, xbc, zbc aabc, aBC, NULL
*bc? aaaabcd, abcd, bcd abcdef, bcdef, NULL
[a-z]bc abc, ebc, zbc 2bc, +bc, Abc, NULL
[^abc]* deee, feee, zyx, z azzz, byy, d, NULL
a[bxy]c abc, axc, ayc a2c, azc, aBc, NULL
*[xyz] abcx, eeeez abcd, eeee, NULL
1|2|35 1, 2, 35 4, 5, 6, NULL
aa|bb|cc aa, bb, cc ab, dd, NULL
\\abc* \abc, \abcdef abc, NULL
\*bc *bc abc, bc, NULL
*\[?\]* [a], a[b]c, xx[y]zz a[bb]c, a[]c, NULL