Syntax:
unary-op expr | expr binary-op expr | expr [ NOT ] like-op expr [ ESCAPE expr ] | ( expr ) | [table-name.]column-name | literal-value | parameter | function-name (expr-list | * ) | expr ISNULL | expr NOTNULL | expr [ NOT ] BETWEEN expr AND expr | expr [ NOT ] IN ( value-list ) | expr [ NOT ] IN ( select-statement ) | expr [ NOT ] IN ( table-name ) | [ EXISTS ] ( select-statement ) | CASE [ expr ] ( WHEN expr THEN expr )* [ ELSE expr ] END | CAST ( expr AS type ) like-op: LIKE | GLOB | REGEXP
This section is devoted to expressions, which are subcomponents of the majority of other commands.
The following unary operators are supported:
Table 43.1. Unary operators
| Symbol | Meaning | Comments |
|---|---|---|
| + | no-op | When applied, thie '+' operator always returns the value of the operand |
| - | arithmetic negation | |
| ~ | bit NOT | the use of this operator is supported only for integer types |
| NOT | negation (boolean NOT) | when used with a numerical operand this operator will return 0 for any value of the operand other than 0. If the operand has value 0, the NOT operator will return 1. |
GDBase understands the following binary operators, in order from highest to lowest precedence:
Table 43.2. Binary operators
| Precedence | Symbol | Meaning | Comments |
|---|---|---|---|
| 1 | || | concatenation | returns a string result |
| 2 | * | arithmetic multiplication | |
| / | arithmetic divison | ||
| % | remainder | returns the remainder of the left operand modulo the right operand | |
| 3 | + | arithmetic sum | |
| - | arithmetic difference | ||
| 4 | << | bitwise left shift | shifts the bits in the left argument to the left by the number of places specified in the right argument. |
| >> | bitwise right shift | shifts the bits in the left argument to the right by the number of places specified in the right argument. | |
| & | bit AND | ||
| | | bit OR | ||
| 5 | < | less than | the result is 1 if true and 0 if false |
| <= | less than or equal to | ||
| > | greater than | ||
| >= | greater than or equal to | ||
| 6 | = | equals | |
| == | |||
| != | not equals | ||
| <> | |||
| IN | membership | checks whether the left argument is in the list specified as the right argument. Returns 0 if false and 1 if true. | |
| LIKE | string pattern match | The right operand contains the pattern string, the left one contains the string to match against the pattern. LIKE is case insensitive, while GLOB is case sensitive. See the text below for more details. | |
| GLOB | |||
| REGEXP | |||
| 7 | AND | conjunction (boolean AND) | |
| 8 | OR | alternative (boolean OR) |
The result of any binary operator is a numeric value, except for the || concatenation operator which returns a string result.
A literal value is an integer or floating point number. Scientific notation is supported. The "." character is always used as the decimal point even if the locale setting specifies ",". The use of "," for the decimal point would result in syntactic ambiguity. A string constant is formed by enclosing the string in single quotes ('). A single quote within a string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not part of the SQL standard.
The LIKE operator does a pattern matching comparison. The right operand contains the pattern, the left operand contains the string to match against the pattern. A percent symbol % in the pattern matches any sequence of zero or more characters in the string. An underscore _ in the pattern matches any single character in the string. Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching).
If the optional ESCAPE clause is present, the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol, underscore or itself matches a literal percent symbol, underscore or escape character in the string, respectively. The infix LIKE operator is implemented by calling the user function like(X,Y).
The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other. Only Polish upper case characters will not match lower case characters.
The GLOB operator is similar to LIKE, but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the user function glob(X,Y) and can be modified by overriding that function.
The REGEXP operator is a special syntax for the regexp() user function. It enables writting regular expressions, compatible with PCRE standard, version 7.2. The result of the REGEXP operator is 1 if a match for the regular expression specified as the right argument is found in the left argument and 0 if no match is found. If any of the arguments is null then null is returned.
When using regular expressions in sql: blocks in Gython scripts, the double dollar sign $$ must be used instead of the single dollar sign $, due to the way in which the $ is interpreted in sql: blocks.
Example 43.24. Checking the format of telephone numbers.
table 'TAB1':
id number
1 '500123456'
2 '500-123-123'
3 '0500123789'
4 '600111222'
5 'tel: 600123321'
6 '+48500111222'
7 '0226942500'
8 '226942500 (work)'
# display which telephone numbers from TAB1 are in the format DDDDDDDDD
sql TAB2:
SELECT *, number REGEXP '^\d{9}$$' AS standard from TAB1
print TAB2
Output:
id | number | standard |
+--+------------------+----------+--
1 | 500123456 | 1 |
2 | 500-123-123 | 0 |
3 | 0500123789 | 0 |
4 | 600111222 | 1 |
5 | tel: 600123321 | 0 |
6 | +48500111222 | 0 |
7 | 0226942500 | 0 |
8 | 226942500 (work) | 0 |
The art of constructing regular expressions is a very broad subject. For an introduction see Oracle's Java tutorial on regular expressions. A detailed treatment of regular expressions can be found at the PCRE website.
A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: "ROWID", "OID", or "_ROWID_". These special identifiers all describe the unique random integer key (the "row key") associated with every row of every table. The special identifiers only refer to the row key if the CREATE TABLE statement does not define a real column with the same name. Row keys act like read-only columns. SELECT * ... does not return the row key.
The SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the operand of the EXISTS operator.
As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECT statements (connected with keywords like UNION or EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to a value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then SELECT is reevaluated every time it is needed.
When SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may be preceded by the NOT keyword to invert the sense of the test.
When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of SELECT becomes the value used in the expression. If SELECT yields more than one result row, all rows after the first are ignored. If SELECT yields no rows, then the value of the SELECT is NULL.
The CAST statement changes the datatype of the argument into the type specified by <type>. <type> can be any non-empty type name that is valid for the type in a column definition of a CREATE TABLE statement.
Both simple, aggregate and window functions are supported as operator arguments. A simple function can be used in any expression. Aggregate and window functions may only be used in a SELECT statement.