Query by example / Usage |
The CONSTRUCT instruction supports a specific query syntax, using wildcard characters and comparison operators.
The table below lists CONSTRUCT wildcard characters that can be used during a query by example input:
Symbol | Meaning | Data type |
---|---|---|
value | Use value as is to filter | Any simple type |
= | Is NULL | Any simple type |
== | Equal to | Any simple type |
> | Greater than | Any simple type |
>= | Greater than or equal to | Any simple type |
< | Less than | Any simple type |
<= | Less than or equal to | Any simple type |
<> or != | Not equal to | Any simple type |
!= | Not NULL | Any simple type |
: or .. | Range of values | Any simple type |
| | List of values | Any simple type |
* | Wildcard for any string | Char string types |
? | Single-character wildcard | Char string types |
[c1-c2] | Range of characters | Char string types |
[c1c2...] | Set of characters | Char string types |
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, according to 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.
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? | aaaabc, 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 |