Expressions
BETWEEN
X [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] Y AND Z
A BETWEEN expression returns TRUE if X is inclusively between
Y and Z. Or, put another way X >= Y AND X <= Z.
If SYMMETRIC is provided, the orders of Y and Z are irrelevant. Such
that they will be swapped if Y > Z.
If neither ASYMMETRIC nor SYMMETRIC is provided then ASYMMETRIC is
assumed.
Examples
30 BETWEEN 25 AND 45 -- TRUE
30 BETWEEN 45 AND 32 -- FALSE
30 NOT BETWEEN 25 AND 45 -- FALSE
30 BETWEEN SYMMETRIC 45 AND 25 -- TRUE
IS NULL
X IS [ NOT ] NULL
Tests if X is (or is not) NULL.
LIKE
X [ NOT ] LIKE Y
Tests if X matches the pattern Y. This is very useful for testing the
prefix or suffix of a string. The ESCAPE clause is not supported yet.
Matching is always against the entire string (not a partial match) and is
case-sensitive. You can use the following characters in Y:
Operator |
Description |
|---|---|
|
Matches any sequence of zero or more characters. |
|
Matches any single character. |
Examples
'a' LIKE 'a' -- TRUE
'a' LIKE 'A' -- FALSE
'ab' LIKE 'a_' -- TRUE
'abc' LIKE 'a_' -- FALSE
'acdeb' LIKE 'a%b' -- TRUE
'abc' NOT LIKE 'a%' -- FALSE
NEXT VALUE FOR
NEXT VALUE FOR X
Returns the next value for a SEQUENCE called X.
All NEXT VALUE FOR expressions for the same sequence return the same value
within a row. Each new row will get the next value.
Examples
CREATE SEQUENCE my_sequence;
VALUES NEXT VALUE FOR my_sequence, NEXT VALUE FOR my_sequence;
VALUES ROW(NEXT VALUE FOR my_sequence, NEXT VALUE FOR my_sequence);
-- msg: CREATE SEQUENCE 1
-- COL1: 1
-- COL1: 2
-- COL1: 3 COL2: 3
SIMILAR TO
X [ NOT ] SIMILAR TO Y
Tests if X matches the similar-expression of Y. The pattern for Y is
a superset of the pattern for LIKE expressions that makes patterns closer to
regular expressions.
Matching is always against the entire string (not a partial match) and is
case-sensitive. You can use the following characters in Y:
Operator |
Description |
|---|---|
|
Matches any sequence of zero or more characters. |
|
Matches any single character. |
|
Denotes alternation (either of two alternatives). |
|
Repeat the previous item zero or more times. |
|
Repeat the previous item one or more times. |
|
Repeat the previous item zero or one time. |
|
Repeat the previous item exactly m times. |
|
Repeat the previous item m or more times. |
|
Repeat the previous item at least m and not more than n times. |
|
Parentheses group items into a single logical item. |
|
Matches any one of the characters inside the brackets, such as
|
|
Matches any one character that is not inside the brackets. |
|
Matches any one character from before the |
|
Inside brackets, matches a character from a class: |
The characters [ ] ( ) | ^ - + * _ % ? { always have a special meaning. A
pattern that uses them in any other way, or that is not complete (such as
'(a'), is an invalid regular expression (SQLSTATE 2201B). The repeat counts
for {m} must be at least 1.
Examples
'abc' SIMILAR TO 'abc' -- TRUE
'abc' SIMILAR TO '_b_' -- TRUE
'abc' SIMILAR TO '_A_' -- FALSE
'abc' SIMILAR TO '%(b|d)%' -- TRUE
'abc' SIMILAR TO '(b|c)%' -- FALSE
'AbcAbcdefgefg12efgefg12' SIMILAR TO '((Ab)?c)+d((efg)+(12))+' -- TRUE
'aaaaaab11111xy' SIMILAR TO 'a{6}_[0-9]{5}(x|y){2}' -- TRUE
'$0.87' SIMILAR TO '$[0-9]+(.[0-9][0-9])?' -- TRUE
'abab' SIMILAR TO '%a%b' -- TRUE
'A1' SIMILAR TO '[[:UPPER:]][^a-z]' -- TRUE