2.7.5 Predicates
(1) Comparison predicate
- Format
row-value-constructor
{= | <> | ^= | != | < | <= | > | >=} row-value-constructor
- Case in which the predicate is TRUE
- Case involving one row value constructor
The predicate is TRUE if the right and left row value constructors satisfy the comparison condition.
If either row value constructor is the null value, the result of the comparison is indefinite.
- Case involving two or more row value constructors
- (=)
- The result of the comparison is TRUE if the relationships between the corresponding elements in the right and left row value constructors are all =.
- The result of the comparison is FALSE if one or more combinations of elements exist in which the relationship <> holds.
- The result of the comparison is indefinite if one or more of the elements being compared is the null value, even when there is no combination of elements in which the relationship <> holds.
(Examples of the result of a comparison being TRUE)- (1,2,3) = (1,2,3)
- ('A','B','C') = ('A','B','C')
- (<> | ^= | !=)
- The corresponding elements in the right and left row value constructors are compared, and the result is TRUE if at least one combination exists in which the relationship <> holds.
- The result is FALSE if the relationship between the corresponding elements is entirely =.
- The result is FALSE if there is no combination in which the relationship <> holds and one or more elements being compared has the null value.
(Examples of the result of a comparison being TRUE)- (1,2,3) <> (1,5,3)
- ('A','B','C') <> ('C','A','B')
- (<)
- The corresponding elements in the right and left row value constructors are compared from left to right, as long as the relationship = holds. The result is TRUE if the relationship < holds between the first elements for which = does not hold.
- The result is FALSE if the relationship between the first elements for which = does not hold is >, and if the relationship = holds between all corresponding elements.
- The result is indefinite if the first elements for which = does not hold contain the null value.
(Examples of the result of a comparison being TRUE)- (1,2,3) < (3,1,2)
- This is TRUE because the relationship between the first elements is 1< 3.
- ('A','B','C','D') < ('A','B','E','A')
- The elements are compared from left to right, and the result is TRUE because the relationship between the first elements for which = does not hold is 'C' < 'E'.
- (>)
- The corresponding elements in the right and left row value constructors are compared from left to right as long as = holds. The result is TRUE if the relationship > holds between the first elements for which = does not hold.
- The result is FALSE if the relationship between the first elements for which = does not hold is a <, or if the relationship = holds between all corresponding elements.
- The result is indefinite if the first elements for which = does not hold contain the null value.
(Examples of the result of a comparison being TRUE)- (1,2,3) > (1,1,5)
- The elements are compared from left to right. The result is TRUE because the relationship between the first elements for which = does not hold is 2 > 1.
- ('A','A','C') > ('A','A','A')
- The elements are compared from left to right. The result is TRUE because the relationship between the first elements for which = does not hold is 'C' > 'A'.
- (<=)
- The corresponding elements in the right and left row value constructors are compared from left to right as long as the relationship = holds. The result is TRUE if the relationship between the first elements for which = does not hold is a <, or if the relationship = holds between all corresponding elements.
- Notice that the result is FALSE if the relationship between the first elements for which = does not hold is a >.
- The result is indefinite if the first elements for which = does not hold contain the null value.
- (>=)
- The corresponding elements in the right and left row value constructors are compared from left to right as long as the relationship = holds. The result is TRUE if the relationship between the first elements for which = does not hold is a >, or if the relationship = holds between all corresponding elements.
- Notice that the result is FALSE if the relationship between the first elements for which = does not hold is a <.
- The result is indefinite if the first elements for which = does not hold contain the null value.
Each comparison predicate can be expanded into a form using Boolean operations. The following table lists forms in which comparison predicates are expanded using Boolean operations.
Operator | Coding using a row value constructor | Coding using Boolean operations |
---|
= | (Rx1,Rx2,...,Rxn) = (Ry1,Ry2,...,Ryn) | Rx1= Ry1 AND Rx2 = Ry2 AND ... AND Rxn = Ryn |
<> | (Rx1,Rx2,...,Rxn) <> (Ry1,Ry2,...,Ryn) | Rx1<> Ry1 OR Rx2 <> Ry2 OR ... OR Rxn <> Ryn |
< | (Rx1,Rx2,Rx3,...,Rxn) < (Ry1,Ry2,Ry3,...,Ryn) | Rx1< Ry1 OR (Rx1 = Ry1 AND Rx2 < Ry2) OR (Rx1 = Ry1 AND Rx2 = Ry2 AND Rx3 < Ry3) OR ... OR (Rx1 = Ry1 AND Rx2 = Ry2 AND Rx3 = Ry3 AND ... AND Rxn-1 = Ryn-1 AND Rxn < Ryn) |
> | (Rx1,Rx2,Rx3,...,Rxn) > (Ry1,Ry2,Ry3,...,Ryn) | Rx1 > Ry1 OR (Rx1 = Ry1 AND Rx2 > Ry2) OR (Rx1 = Ry1 AND Rx2 = Ry2 AND Rx3 > Ry3) OR ... OR (Rx1 = Ry1 AND Rx2 = Ry2 AND Rx3 = Ry3 AND ... AND Rxn-1 = Ryn-1 AND Rxn > Ryn) |
- Rules
- On both sides of the comparison operators (=, <>, ^=, !=, <, <=, >, >=), row value constructors consisting solely of literals can be specified.
- Values that yield any of the following data types as a result of an operation cannot be specified:
- BLOB
- BINARY with a minimum defined length of 32,001 bytes
- BOOLEAN
- Abstract data type
- For row value constructors, see 2.8 Row value constructors.
- Row value constructor elements that are located in the corresponding positions in the right and left row value constructors are treated as corresponding values. The corresponding values must have data types that are comparable.
![[Figure]](figure/zu2s0115.gif)
- A subquery cannot be specified in a comparison predicate in any of the following locations:
- A search condition in an IF statement
- A search condition in a WHILE statement
- A WHEN search condition in CREATE TRIGGER (trigger action search condition)
- When specifying a repetition column, specify a subscript. If a subscripted repetition column is specified and if its elements satisfy the condition, the result of the comparison with the corresponding value is TRUE.
- The word ANY can be specified as a repetition column subscript. If ANY is specified and if at least one element in the column satisfies the condition, the result of the comparison is TRUE. If the result of the comparison is not TRUE and if the condition specified on at least one element of the column is indefinite, the result of the comparison is indefinite. If the result of the comparison is not TRUE or indefinite, the result of the comparison is FALSE.
- If a subscripted repetition column is specified and the subscripted elements do not exist, the result of the comparison with the corresponding value is indefinite.
- The row value constructors that are to be compared must have the same number of result columns.
- A leap second is treated as a value smaller than one minute.
Example:
'00:00:61' < '00:01:00'
(2) NULL predicate
- Format
value-expression IS [NOT] NULL
- Conditions under which a predicate is TRUE
The NULL predicate is TRUE for rows in which the value of the specified value expression is a null value. If NOT is specified, it is TRUE for rows in which the value of the specified value expression is not a null value. For details about the NULL value, see 1.7 Null value.
- Rules
- Value expressions of the following data types cannot be specified:
- BLOB (except for the ? parameter or when specified with only one embedded variable)
- BINARY with a minimum defined length of 32,001 bytes (except for the ? parameter or when specified with only one embedded variable)
- BOOLEAN
- If an unsubscripted repetition column is specified and the column does not contain any elements (in the case of the NOT specification, if at least one element is present), the NULL predicate will be TRUE. Even if all the elements in the column are NULL, the NULL predicate will not be TRUE.
- If a subscripted repetition column is specified and a specified element is NULL, the NULL predicate will be TRUE.
- If a subscripted repetition column is specified and the column does not contain any elements, the NULL predicate will be UNKNOWN.
- When a subscripted repetition column is specified, ANY can be specified as a subscript. When ANY is specified and at least one element in the column satisfies the specified conditions, the NULL predicate will be TRUE.
- Note
- The NULL predicate is TRUE if overflow error suppression is set or if there is a null value due to an overflow of the operation result of the value expression.
(3) IN predicate
- Format
row-value-constructor [IS] [NOT] IN
{(row-value-constructor [,row-value-constructor]...)
|(table-subquery)
([SQL-optimization-specification-for-subquery-execution-method]
SELECT[{ALL | DISTINCT}]{selection-expression|*}
(table-expression)
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [,value-expression]...]
[HAVING search-condition])}
- Conditions under which a predicate is TRUE
The IN predicate is TRUE if any of the following conditions is satisfied:
- The left-hand side row value constructor matches any row value constructor on the right-hand side.
- The left-hand side row value constructor matches any result row for a table subquery.
If NOT is specified, the IN predicate is TRUE with respect to rows for which the row value constructor on the left-hand side does not match any of the result rows for any row value constructor or table subquery that is specified on the right-hand side.
- Rules
- Values in which the data items as a result of a row value constructor or table subquery take any of the following data types:
- BLOB
- BINARY with a minimum defined length of 32,001 bytes
- BOOLEAN
- Abstract data type
- A maximum of 30,000 row value constructors can be specified on the right-hand side.
- A row value constructor consisting solely of a value specification cannot be specified on the left side of an IN predicate that is not a table subquery.
- For table subqueries, see 2.4 Subqueries.
- Some IN predicates have the same meaning as a quantified predicate. The following predicates are synonymous:
IN predicate | Quantified predicate |
---|
row-value-constructor IN table-subquery | row-value-constructor = ANY table-subquery or row-value-constructor = SOME table-subquery |
row-value-constructor NOT IN table-subquery | row-value-constructor <> ALL table-subquery |
- If the result of a table subquery is the empty set, the result of the IN predicate is FALSE; the result, however, is TRUE if NOT is specified.
- A table subquery cannot be specified in an IN predicate in the following locations:
- Search condition of an IF statement
- Search condition of a WHILE statement
- WHEN search condition (trigger action condition) of CREATE TRIGGER
- In the row value constructors, the row value constructor elements and the selection expressions of table subqueries in the corresponding positions are treated as corresponding values. The corresponding values must have mutually convertible or comparable data types. If, however, the result data type of a row value constructor element in a row value constructor specified on the left-hand side is national character data, and a character string literal is specified as the corresponding value, the character string literal is treated as a national character string literal. If a character string literal is treated as a national character string literal, HiRDB only checks the length of the character data without checking the character code.
![[Figure]](figure/zu2s0116.gif)
- When specifying a repetition column, a subscript must be specified. If a subscripted repetition column is specified and its elements satisfy specified conditions, the IN predicate is TRUE.
- The word ANY can be specified as a subscript for a repetition column. If ANY is specified and if at least one element in the column satisfies the specified condition, the IN predicate is TRUE. If the IN predicate is not TRUE and if a condition specified with respect to at least one element in the column is indefinite, the IN predicate becomes indefinite. Any IN predicate that is not TRUE or that is not indefinite is FALSE.
- A repetition column with an ANY subscript cannot be specified in the row value constructor on the right side.
- If a repetition column is specified with a subscript and the column contains no elements, the IN predicate is indefinite.
- If the result of a row value constructor is the null value, the result of the comparison of the corresponding value is indefinite.
- The row value constructors that are subject to comparison must have the same number of result columns.
- Note
- If a table subquery is specified in an IN predicate, in some cases HiRDB creates a work table. In this process, the processing of the subquery in the IN predicate may be subject to restrictions depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 9 Installation and Design Guide.
(4) LIKE predicate
- Format
value-expression [NOT] LIKE pattern-character-string [ESCAPE
escape-character]
- Conditions under which a predicate is TRUE
The LIKE predicate is TRUE for a row in which the value of a specified value expression matches the pattern represented by a pattern character string. If NOT is specified, the LIKE predicate is TRUE for those rows for which the value of the specified column does not match the pattern expressed by the pattern character string.
- value-expression
- Specifies the value expression that will be the object of a character string pattern comparison.
However, value expressions in which only values other than SQL variables or SQL parameters are specified cannot be specified.
- The following data types can be specified in a value expression or a pattern character string: character string data, national character string data, mixed character string data, or BINARY with a maximum defined length of 32,000 bytes.
- When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the LIKE predicate will be TRUE.
- ANY can be specified as a subscript for a repetition column. When ANY is specified and at least one element in the column meets specified conditions, the LIKE predicate will be TRUE. If the LIKE predicate is not TRUE and a condition specified for at least one element in the column is UNKOWN, the LIKE predicate will be UNKNOWN. If the LIKE predicate is neither TRUE nor UNKNOWN, it will be FALSE.
- If a repetition column is specified with a subscript and the column contains no elements, the LIKE predicate will be UNKNOWN.
- pattern-character-string
- Specifies a value specification.
- A specifiable pattern character string must have the same data type as the data type of the value expression.
- The following combinations of value expression data types and pattern character string data types are allowed:
Value expression data types | Pattern character string or escape character string data type |
---|
Character string data | National character string data | Mixed character string data | Binary data |
---|
Default character set | EBCDIK | UTF16 |
---|
Character string data | Default character set | Y | N | N | N | Y | N |
EBCDIK | R#3 | Y | N | N |
UTF16 | R#3 | N | Y |
National character string data | R#1 | N | N | Y | N | N |
Mixed character string data | Y | N | N | N | Y | N |
Binary data | R#2 | N | N | N | N | Y |
- Legend:
- Y: Can be specified.
- R: Can be specified with restrictions.
- N: Cannot be specified.
- #1
- Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, make sure to specify it using a double-byte character.
- #2
- Only a hexadecimal character string literal can be specified.
- #3
- Only a character string literal, embedded variable, or ? parameter can be specified.
- Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
Character string literal
Embedded variable (default character set)
? parameter
- The special characters that can be included in the pattern character string include the underscore (_), percent sign (%), and escape character. Specify the code for the special characters as binary data. The following table describes how the special characters underscore and percent sign are interpreted in the pattern character string.
Table 2-3 Meaning of special characters in the pattern character string (LIKE predicate)
Special character | Item specification data type | Meaning of the special character | Method of specifying the special character |
---|
Underscore (_) | Character string data | Default character set#1 | Any single character | Single-byte underscore (_) |
EBCDIK#1 |
UTF16 |
National character string data | Double-byte underscore ( ) |
Mixed character string data | Single-byte underscore (_) |
Binary data | Any single byte | 5f (hex code for an underscore)#3 |
Percent sign (%) | Character string data | Default character set#1 | A character string of any length (one or more characters) | Single-byte percent sign (%) |
EBCDIK#1 |
UTF16 |
National character string data#2 | Double-byte percent sign ( ) |
Mixed character string data | Single-byte percent sign (%) |
Binary data | A byte string of any length (one or more bytes) | 25 (hex code for percent sign)#3 |
- #1
- All characters or data values are treated as single-byte characters.
- #2
- All characters or data values are treated as double-byte characters.
- #3
- When specifying special characters in binary data, specify the code indicating the special character (_ or %) using the character encoding specified when HiRDB was set up.
- If the pattern character string does not contain the % sign and the data lengths of the column to be compared and the pattern character string are different, the LIKE predicate is not TRUE.
- If the character string and the pattern character string specified in value-expression are variable-length data (VARCHAR, NVARCHAR, MVARCHAR, or BINARY), HiRDB compares the data lengths in addition to the data and the pattern character string data in the value-expression.
- When an embedded variable, an SQL variable, or an SQL parameter is specified as a pattern character string point, the following must be observed:
If a pattern character string formed with a fixed-length embedded variable, SQL variable, or SQL parameter and if a pattern character string shorter than the length of the variable is set in that variable, the variable may be filled with trailing spaces or with any remaining invalid characters as values. If a retrieval is attempted using this type of pattern character string, target data that does not have matching trailing spaces or matching leftover characters will not be retrieved.
To avoid this problem when a fixed-length variable is used as a pattern character string, we recommend that the percent sign be set at the end of the variable.
- Example:
- In the following example, character string data containing 'ABCD' gives the indicated results if pattern character strings 'AB%' and 'AB%%' are assigned to variables:
Data type of variable | Pattern character string | Character string data | Result of comparison |
---|
Variable-length character string | 'AB%' | 'ABCD' | Matches. |
'AB%%' | 'ABCD' | Matches. |
Fixed-length character string (4 bytes) | 'AB% ' | 'ABCD' | Does not match. |
'AB%%' | 'ABCD' | Matches. |
- Example:
- A comparison between binary data 52454452554d and a variable in which a pattern character string, X'52454425', is set gives the following results:
Data type of variable | Pattern character string | Binary data | Result of comparison |
---|
BINARY type | X'52454425' | 52454452554d | Matches |
X'5245442525' | 52454452554d | Matches |
Examples of pattern character strings
The following table provides examples of typical pattern character strings used in the LIKE predicate.
Table 2-4 Typical pattern character strings used in the LIKE predicate
Item | Pattern character string | Meaning | Example |
---|
Pattern character string | Pattern-matching character string |
---|
Front match | nnn% | The leading portion of the character string is nnn. | 'ACT%' | Character strings beginning with "ACT", such as ACT, ACTOR, and ACTION. |
Rear match#1 | %nnn | The trailing portion of the character string is nnn. | '%ING' | Character strings ending with "ING", such as ING, BEING, and HAVING. |
Any match | %nnn% | The character string contains nnn at any position. | N'%OR%' | Character strings containing "or", such as OR, More, and CoLoR.#2 |
Complete match | nnn | The character string is equal to nnn. | 'EQUAL' | EQUAL |
Partial match | _..._nnn_..._ | A specific portion of the character string is equal to nnn; the other portions of the character string contain any characters. | '_I_' | 3-letter character strings, in which the second character is "I", such as BIT, HIT, and KIT. |
Other | nnn%mmm | The leading portion of the character string is nnn and the trailing portion is mmm. | 'O%N' | Character strings that begin with O and end with N such as ON, OWN, and ORIGIN. |
%nnn%mmm% | The character string contains nnn at any position and mmm at a subsequent position. | '%O%N%' | Character strings that contain O, and have an N in any subsequent position, such as ON, ONE, DOWN, and COUNT. |
nnn_..._ mmm% | The leading portion of the character string is nnn and the trailing portion is mmm. | 'CO_ _ ECT%' | Character strings that begin with "CO" and contain the string "ECT" in the 5th through 7th character positions, such as CORRECT, CONNECTER, and CONNECTION |
#1: Because the space is regarded as a character for comparison purposes, a comparison with data that has trailing spaces produces the FALSE result.
#2: In a national character string, the _ and % special characters are coded using the _ and % national characters.
- Note
- nnn and mmm denote any character strings that do not contain % or _.
- escape character
- Any underscore or percent sign coded in a pattern character string is unconditionally treated as a special character; these characters cannot be treated as regular characters. When a special character is to be specified as a regular character, an escape character must be specified. Specifying any character after the ESCAPE keyword (an escape character) causes the special character following the escape character coded in the pattern character string to be treated as a regular character.
- Example 1
- A character string containing a '5%', such as '5%' and '25%':
- '%5?%%' ESCAPE '?'
- Example 2
- A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':
- '%PRINT@_REC' ESCAPE '@'
- Example 3
- A hexadecimal character string containing X'48695244425f' in the binary string, such as X'48695244425f':
- X'4869524442ee5f' ESCAPE X'ee'
- The following characters can be specified as escape characters:
Data type of item | Character that can be specified |
---|
Character string data (CHAR, VARCHAR) | Default character set | Any single character (single-byte character)#1 |
EBCDIK |
UTF16 | Any single character#2 |
Mixed character string data (MCHAR, MVARCHAR) | Any single character#2 |
National character data (NCHAR, NVARCHAR) | Any single character (double-byte character)#3 |
Binary data (BINARY) | Any single byte value |
- Note
- Care must be taken that the special character is specified following the escape character.
- #1
- All characters or data values are treated as single-byte characters.
- #2
- Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
- #3
- All characters or data values are treated as double-byte characters.
- Notes
- For improved performance, the defined length of the column used in a value expression on the left-hand side of LIKE should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, MVARCHAR, or BINARY) or a maximum of 127 characters (NCHAR or NVARCHAR).
- Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of LIKE predicate is true.
Example
Execute the following query in a condition where the query is set up in sjis character code, a CHAR-type column C1 is in Table T1, and a row
is in column C1:
SELECT C1 FROM T1 WHERE C1 LIKE '%A%' ;
The character code for the character
in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for
, which is a multi-byte character, includes the character code for the single-byte character A, the result of the LIKE predicate is true.
(5) XLIKE predicate
- Format
value-expression [NOT] XLIKE pattern-character-string [ESCAPE escape-character]
- Conditions under which a predicate is TRUE
The XLIKE predicate is TRUE for a row in which the value of a specified value expression matches the pattern represented by a pattern character string. If NOT is specified, the predicate is TRUE for a rows for which the value does not match the pattern character string. The comparison performed is not case-sensitive.
- value-expression
- Specifies the value expression that will be the object of a character string pattern comparison.
However, value expressions in which only values other than SQL variables or SQL parameters are specified cannot be specified.
- Character string data, national character data, or mixed character string data can be specified as the data type of a value expression.
- When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the XLIKE predicate will be TRUE.
- ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the XLIKE predicate will be TRUE. If the XLIKE predicate is not TRUE and a condition specified for at least one element in the column is UNKOWN, the XLIKE predicate will be UNKNOWN. If the XLIKE predicate is neither TRUE nor UNKNOWN, it will be FALSE.
- If a repetition column is specified with a subscript and the column contains no elements, the XLIKE predicate will be UNKNOWN.
- pattern-character-string
- A value specification must be specified in the pattern character string.
- Any data type that can be specified in an value expression can be specified in a pattern character string.
- The following combinations of value expression data types and pattern character string data types are allowed:
Data type of value expression | Data type of pattern character string or escape character |
---|
Character string data | National character string data | Mixed character string data |
---|
Default character set | EBCDIK | UTF16 |
---|
Character string data | Default character set | Y | N | N | N | Y |
EBCDIK | R#2 | Y | N | N |
UTF16 | R#2 | N | Y |
National character string data | R#1 | N | N | Y | N |
Mixed character string data | Y | N | N | N | Y |
- Legend:
- Y: Can be specified.
- R: Can be specified with restrictions.
- N: Cannot be specified.
- #1
- Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, be sure to specify it using a double-byte character.
- #2
- This can specify only a character string literal, embedded variable, or ? parameter.
- Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
Character string literal
Embedded variable (default character set)
? parameter
- The following special characters can be used in a pattern character string: the underscore, the percent sign, and escape characters. The following table describes how the special characters underscore and percent sign are interpreted in the pattern character string.
Table 2-5 Meanings of special characters in pattern character strings (XLIKE predicate)
Special character | Data type of item specification | Meaning of the special character | Method of specifying the special character |
---|
Underscore (_) | Character string data | Default character set#1 | Any single character | Single-byte underscore (_) |
EBCDIK#1 |
UTF16 |
National character string data | Double-byte underscore ( ) |
Mixed character string data | Single-byte underscore (_) |
Percent sign (%) | Character string data | Default character set#1 | A character string of any length (one or more characters) | Single-byte percent sign (%) |
EBCDIK#1 |
UTF16 |
National character string data#2 | Double-byte percent sign ( ) |
Mixed character string data | Single-byte percent sign (%) |
- #1
- All characters or data values are treated as single-byte characters.
- #2
- All characters or data values are treated as double-byte characters.
- Comparison of the following characters with pattern character string data is not case-sensitive:
Alphanumeric national characters and mixed characters
- This predicate is not TRUE if a percent sign does not occur in the pattern character string or the column data and the pattern character string differ in length.
- If the character string and pattern character string specified in the value expression are both variable-length character strings (VARCHAR, NVARCHAR, or MVARCHAR), HiRDB compares the character string lengths, as well as the character string data and the pattern character string data.
- When an embedded variable, an SQL variable, or an SQL parameter is to be specified as a pattern character string, the following point must be observed:
If a pattern character string is formed with a fixed-length embedded variable, SQL variable, or SQL parameter and if a pattern character string shorter than the length of the variable is set in that variable, the variable may be filled with trailing spaces or with any remaining invalid characters as values. When this type of pattern character string is used as a search string, data that does not similarly contain trailing spaces or invalid characters cannot be retrieved. Therefore, when using a fixed-length variable as a pattern character string, the variable must be filled with trailing percent signs.
- Examples of pattern character strings
The following table provides examples of typical pattern character strings used in the XLIKE predicate.
Table 2-6 Examples of pattern character strings used in the XLIKE predicate
Item | Pattern character string | Meaning | Example |
---|
Pattern character string | Pattern-matching character string |
---|
Front match | nnn% | The leading portion of the character string is nnn. | 'ACT%' | Character strings beginning with "ACT"#1, such as ACT, Actor, and action. |
Rear match | %nnn | The trailing portion of the character string is nnn. | '%ING' | Character strings ending with "ING"#2, such as Ing, Being, and HAVING. |
Any match | %nnn% | The character string contains nnn at any position. | '%OR%' | Character strings containing "or"#3, such as OR, More, and CoLoR. |
Complete match | nnn | The character string is equal to nnn. | 'MAX' | Character strings such as MAX, max, and mAx#4 |
Partial match | _..._nnn_..._ | A specific portion of the character string is equal to nnn, the other portions of the character string contain any characters. | '_I_' | A three-character character string in which the second character is either "I" or "i", such as Bit, HIT, and Kit. |
Other | nnn%mmm | The leading portion of the character string is nnn and the trailing portion is mmm. | 'O%N' | Character strings that begin with "O" or "o" and end with "N" or "n", such as on, Own, and ORIGIN. |
%nnn%mmm% | The character string contains nnn at any position and mmm at a subsequent position. | '%O%N%' | Character strings that contain "O" or "o" and have "N" or "n" at a subsequent position, such as ON, one, DowN, and Count. |
nnn_..._ mmm% | The leading portion of the character string is nnn and the trailing portion is mmm. | 'CO_ _ ECT%' | Character strings that begin with "CO"#5, and whose 5th through the 7th characters are "ECT"#6, such as correct, Connecter, and CONNECTION. |
Note 1: nnn and mmm are any character strings that do not contain % or _.
Note 2: Because the space character is also used as a comparison character, comparison is with data containing trailing spaces, this string yields the FALSE result.
Note 3: In a national character string, the special characters (% and _) must be coded as the national character "%" or "_".
#1: One of the character strings ACT, ACt, Act, aCT, aCt, acT, or act
#2: One of the character strings ING, INg, Ing, InG, iNG, iNg, inG, or ing
#3: One of the character strings OR, Or, oR, or or
#4: One of the character strings MAX, MAx, Max, MaX, mAX, mAx, maX, or max
#5: One of the character strings CO, Co, cO, or co
#6: One of the character strings ECT, ECt, Ect, EcT, eCT, eCt, ecT, or ect
- Escape-character
- Any underscore or percent sign coded in a pattern character string is unconditionally treated as a special character; these characters cannot be treated as regular characters. When a special character is to be specified as a regular character, an escape character must be specified. Specifying any character after the ESCAPE keyword (escape character) causes the special character following the escape character coded in the pattern character string to be treated as a regular character.
- Example 1
- A character string containing a '5%', such as '5%' and '25%':
- '%5?%%' ESCAPE '?'
- Example 2
- A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':
- '%PRINT@_REC' ESCAPE '@'
- The following characters can be specified as escape characters:
Data type of item | Character that can be specified |
---|
Character string data (CHAR, VARCHAR) | Default character set | Any single character (single-byte character)#1 |
EBCDIK |
UTF16 | Any single character#2 |
Mixed character string data (MCHAR, MVARCHAR) | Any single character#2 |
National character data (NCHAR, NVARCHAR) | Any single character (double-byte character)#3 |
- Note
- Care must be taken that the special character is specified following the escape character.
- #1
- All characters or data values are treated as single-byte characters.
- #2
- Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
- #3
- All characters or data values are treated as double-byte characters.
- Notes
- For improved performance, the defined length of the column used in a value expression on the left-hand side of XLIKE should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, or MVARCHAR) or a maximum of 127 characters (NCHAR or NVARCHAR).
- Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of the XLIKE predicate is true.
- Example
- Execute the following query in a condition where the query is set up in sjis character code, a CHAR-type column C1 is in Table T1, and a row
is in column C1: - SELECT C1 FROM T1 WHERE C1 XLIKE '%A%' ;
- The character code for the character
in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for
, which is a multi-byte character, includes the character code for the single-byte character A, the result of the XLIKE predicate is true.
(6) SIMILAR predicate
- Format
value-expression [NOT] SIMILAR TO pattern-character-string [ESCAPE escape-character] |
- Conditions under which a predicate is TRUE
The SIMILAR predicate is TRUE for a row in which the value of a specified value expression matches the pattern expressed by a pattern character string. If NOT is specified, the predicate is TRUE for a row in which the value of a specified value expression does not match the pattern expressed by the pattern character string. Note that when the length of the pattern character string is 0, the SIMILAR predicate is TRUE when the length of the value expression is 0.
- Rules
- value-expression
- Specifies the value expression that is to be compared with the character string pattern. However, you cannot specify a value expression that specifies only the ? parameter, or a value of an embedded variable.
- The following data types can be specified in a value expression or a pattern character string: character string data, national character string data, mixed character string data, or BINARY with a maximum defined length of 32,000 bytes.
- When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the SIMILAR predicate will be TRUE.
- ANY can be specified as a subscript for a repetition column. When ANY is specified, the SIMILAR predicate will be TRUE as long as at least one of the elements of the column satisfies the condition. If the SIMILAR predicate is not TRUE and if the condition specified for at least one of the elements of the column is unknown, the SIMILAR predicate will be FALSE. When the SIMILAR predicate is neither TRUE nor unknown, it is FALSE.
- If a repetition column is specified with a subscript and the column contains no elements, the SIMILAR predicate will be unknown.
- pattern-character-string
- A value expression must be specified in the pattern character string.
- You cannot specify a repetition column for a value expression.
- The following combinations of value expression data types and pattern character string data types are allowed:
Data type of value expression | Data type of pattern character string or escape character |
---|
Character string data | National character string data | Mixed character string data | Binary data |
---|
Default character set | EBCDIK | UTF16 |
---|
Character string data | Default character set | Y | N | N | N | Y | N |
EBCDIK | R#3 | Y | N | N |
UTF16 | R#3 | N | Y |
National character string data | R#1 | N | N | Y | N | N |
Mixed character string data | Y | N | N | N | Y | N |
Binary data | R#2 | N | N | N | N | Y |
- Legend:
- Y: Can be specified.
- R: Can be specified with restrictions.
- N: Cannot be specified.
- #1
- Only a character string literal can be specified in a pattern character string. In this case, the character string literal is treated as a national character string literal. When a character string literal is treated as a national character string literal, the length of the character string data is checked, but the character encoding is not checked. When specifying a special character, make sure to specify it using a double-byte character.
- #2
- This can specify only a hexadecimal character string literal.
- #3
- This can specify only a character string literal, embedded variable, or ? parameter.
- Use the same character set for the value expression, pattern character string, and escape character string. If the pattern character string and escape character string are one of those listed below, however, they are converted to the character set of the value expression, enabling comparison:
Character string literal
Embedded variable (default character set)
? parameter
- The format of a regular expression to be specified for a pattern character string is shown below:
regular-expression ::= normal-term | regular-expression | regular-expression
normal-term ::= normal-factor | normal-term normal-factor
normal-factor ::= normal-primary
| normal-primary *
| normal-primary +
| normal-primary ?
| normal-primary repetition-factor
repetition-factor ::= { lower-limit [ upper-limit-specification ] }
upper-limit-specification ::= , [ upper-limit ]
normal-primary ::= character-specifier
| %
| normal-character-set
| normal-character-set-identifier-specification
| ( regular-expression )
character-specifier ::= non-escape-character
| escape-character
normal-character-set ::= _
| [ character-list... ]
| [ ^ character-list... ]
character-list ::= character-specifier
| character-specifier - character-specifier
| normal-character-set-identifier-specification
normal-character-set-identifier-specification ::= [ : normal-character-set-identifier : ] |
- The syntax rules for a regular expression to be specified for a pattern character string are described below:
Specify one of the following for the normal character set identifier:
'ALPHA', 'UPPER', 'LOWER', 'DIGIT', 'ALNUM', 'SPACE', 'WHITESPACE'
The non-escaped characters include all the individual characters other than the following special characters:
Special character | Code representation in binary data |
---|
No character set specified | Character set specified |
---|
EBCDIK | UTF16 |
---|
_ (underscore) | X'5F' | X' 6D' | U+005F |
% (percent sign) | X' 25' | X' 6C' | U+0025 |
* (asterisk) | X' 2A' | X' 5C' | U+002A |
+ (plus sign) | X' 2B' | X' 4E | U+002B |
? (question mark) | X' 3F' | X' 6F' | U+003F |
| (vertical line) | X' 7C' | X' 4F' | U+007C |
( (left parenthesis) | X' 28' | X' 4D' | U+0028 |
) (right parenthesis) | X' 29' | X' 5D' | U+0029 |
{ (left curly bracket) | X' 7B' | X' C0' | U+007B |
} (right curly bracket) | X' 7D' | X' D0' | U+007D |
[ (left square bracket) | X' 5B' | X' 4A' | U+005B |
] (right square bracket) | X' 5D' | X' 5A' | U+005D |
Escape character | Value specified in ESCAPE |
- (minus sign)# | X' 2D' | X' 60' | U+002D |
: (colon)# | X' 3A' | X' 7A' | U+003A |
^ (circumflex)# | X' 5E' | X' 5F' | U+005E |
#: These symbols are treated as special characters only within a character string.
To specify a special character as a regular character (that is, to escape it), you must specify it following the escape character.
For the lower and upper limits, specify integers that satisfy the following condition: 0
lower limit
upper limit
256.
- The following table explains the meaning of each regular expression specification used in a pattern character string.
Table 2-7 Meaning of each regular expression specification
Regular expression specification | Meaning |
---|
Character specifier | Means a character (character string with a length of 1) specified by a character specifier. |
_ (underscore) | Means a character with a length of 1. |
% (percent sign) | Means a character string with a length of 0 or greater. |
Normal primary* | Means 0 or more repetitions of the preceding normal primary. |
Normal primary+ | Means one or more repetitions of the preceding normal primary. |
Normal primary? | Means 0 or one repetition of the preceding normal primary. |
regular-expression|regular-expression | Means either of the regular expressions specified before and after the vertical bar (|). |
(regular-expression) | Means grouping of the regular expression specified within the parentheses. When a regular expression is to be used, this specification is used to clarify that it is a regular expression. It is used primarily when the vertical bar (|) is used. |
normal-primary{n} normal-primary{n,m} normal-primary{n,} | Means that the preceding normal primary is repeated. The following shows how a repetition count is specified: {n}: Repeats the preceding regular expression n times. {n,m}: Repeats the preceding regular expression at least n times but not more than m times. {n,}: Repeats the preceding regular expression at least n times. |
[character-list...] | Means any of the characters listed. |
[^character-list...] | Means any character except those listed. |
character-specifier-1-character-specifier-2 | When specified in a character string list, means any character between the character indicated by character-specifier-1 and the character indicated by character-specifier-2 (character code range). |
[:ALPHA:] | Any upper-case alphabetic character (excluding \, @, and #) or lower-case alphabetic character |
[:UPPER:] | Any upper-case alphabetic character (excluding \, @, and #) |
[:LOWER:] | Any lower-case alphabetic character |
[:DIGIT:] | Any numeric digit |
[:ALNUM:] | Any upper-case alphabetic character (excluding \, @, and #), lower-case alphabetic character, or numeric digit |
[:SPACE:] | A single-byte space (double-byte space when the value expression is national character string data) |
[:WHITESPACE:] | Any single character from among the characters whose character codes are listed in the table below (what is meant by [:WHITESPACE:] depends on the type of character codes) |
The following table lists the character codes of the characters included in [:WHITESPACE:]:
Unicode(UTF-8) | Shift JIS kanji | EUC Japanese kanji, EUC Chinese kanji | Chinese kanji (GB18030) | LANG-C | Character name in the Unicode standard character set |
---|
Default character set | UTF16 | Default character set | EBCDIK |
---|
X'09' | U+0009 | X'09' | X'05' | X'09' | X'09' | X'09' | Horizontal Tabulation |
X'0A' | U+000A | X'0A' | X'15' | X'0A' | X'0A' | X'0A' | Line Feed |
X'0B | U+000B | X'0B | X'0B' | X'0B | X'0B | X'0B | Vertical Tabulation |
X'0C' | U+000C | X'0C' | X'0C' | X'0C' | X'0C' | X'0C' | Form Feed |
X'0D' | U+000D | X'0D' | X'0D' | X'0D' | X'0D' | X'0D' | Carriage Return |
X'20' | U+0020 | X'20' | X'40' | X'20' | X'20' | X'20' | Space |
X'C285' | U+0085 | -- | -- | -- | X'81308135' | -- | Next Line# |
X'C2A0' | U+00A0 | -- | -- | -- | X'81308432' | -- | No-Break Space# |
X'E19A80' | U+1680 | -- | -- | -- | X'8134AC34' | -- | Ogham Space Mark# |
X'E28080' | U+2000 | -- | -- | -- | X'8136A336' | -- | En Quad# |
X'E28081' | U+2001 | -- | -- | -- | X'8136A337' | -- | Em Quad# |
X'E28082' | U+2002 | -- | -- | -- | X'8136A338' | -- | En Space# |
X'E28083' | U+2003 | -- | -- | -- | X'8136A339' | -- | Em Space# |
X'E28084' | U+2004 | -- | -- | -- | X'8136A430' | -- | Three-Per-Em Space# |
X'E28085' | U+2005 | -- | -- | -- | X'8136A431' | -- | Four-Per-Em Space# |
X'E28086' | U+2006 | -- | -- | -- | X'8136A432' | -- | Six-Per-Em Space# |
X'E28087' | U+2007 | -- | -- | -- | X'8136A433' | -- | Figure Space# |
X'E28088' | U+2008 | -- | -- | -- | X'8136A434' | -- | Punctuation Space# |
X'E28089' | U+2009 | -- | -- | -- | X'8136A435' | -- | Thin Space# |
X'E2808A' | U+200A | -- | -- | -- | X'8136A436' | -- | Hair Space# |
X'E280A8' | U+2028 | -- | -- | -- | X'8136A635' | -- | Line Separator# |
X'E280A9' | U+2029 | -- | -- | -- | X'8136A636' | -- | Paragraph Separator# |
X'E280AF' | U+202F | -- | -- | -- | X'8136A732' | -- | Narrow No-Break Space# |
X'E38080' | U+3000 | 0x8140 | -- | X'A1A1' | X'A1A1' | -- | Ideographic Space# |
- Legend:
- --: Not applicable
- #
- Not included in [:WHITESPACE:] when the value expression is character string type.
- Binary data cannot be specified for a normal character set identifier.
- You should note the following about specifying an embedded variable, SQL variable, or SQL parameter as a pattern character string:
When a fixed-length embedded variable, SQL variable, or SQL parameter is specified as a pattern character string, setting a pattern character string that is shorter than the length of the variable may cause spaces to be entered following the variable, or may set the remaining invalid characters as a value. When such a pattern character string is used for a search, data that is not followed by similar spaces or that does not contain the same value as the invalid characters is not retrieved. Therefore, when using a fixed-length variable as a pattern character string, you should fill the excess positions with the percent sign (%).
Example 1:
The following table lists the comparison results when AB% and AB%% are specified for the pattern character string and the character string data is ABCD.
Variable data type | Pattern character string | Character string data | Comparison result |
---|
Variable-length character string | 'AB%' | 'ABCD' | Matches. |
'AB%%' | 'ABCD' | Matches. |
Fixed-length character string (4 bytes) | 'AB% ' | 'ABCD' | Does not match. |
'AB%%' | 'ABCD' | Matches. |
Example 2:
The following table lists the comparison results when X'52454425 is specified for the pattern character string and the binary data is 52454452554d.
Variable data type | Pattern character string | Binary data | Comparison result |
---|
BINARY type | X'52454425' | 52454452554d | Matches. |
X'5245442525' | 52454452554d | Matches. |
- Invalid pattern character string
- The following table describes the conditions that make a pattern character string invalid (KFPA11424-E message is issued):
Related item | Condition | Examples of invalid pattern character strings |
---|
Normal primary* Normal primary+ Normal primary? | Normal primary preceding * , + , or ? is not specified. | (*), (+), (?) |
regular-expression|regular-expression | A regular expression is not specified on both sides of the vertical bar (|). | a|, (a|), (a||b) |
(regular-expression) | No regular expression is specified within the parentheses. | () |
Left and right parentheses do not match. | (abc, abc) |
normal-primary{n} normal-primary{n,m} normal-primary{n,} | The normal primary that should precede the repetition factor is not specified. | {4} |
The repetition count specified for the repetition factor is invalid. | a{-1}, a{4,2} |
Left and right curly brackets do not match. | a{4, a4} |
[character-list...] [^character-list...] | Character list contains a non-escaped special character. Note that a normal character set identifier can be specified. | [a%c] |
The character specified before or after the minus sign (-) is invalid. | [-], [c-a], [a--] |
No character list is specified within the square brackets. | [], [^] |
Left and right square brackets do not match. | [a-c, a-c] |
Escape character | The last character of a pattern character string is an escape character. | abc\ (when \ is specified as the escape character) |
Normal character set identifier specification | The normal character set identifier is invalid. | [:INVALID:] |
- Examples of pattern character strings
- The following table provides examples of typical pattern character strings used in the SIMILAR predicate.
Table 2-8 Examples of pattern character strings used in the SIMILAR predicate
Item | Pattern character string | Meaning | Example |
---|
Pattern character string | Pattern-matching character string |
---|
Front match | nnn% | The leading portion of the character string is nnn. | 'ACT%' | Character strings beginning with ACT, such as ACT, ACTOR, ACTION. |
Rear match#1 | %nnn | The trailing portion of the character string is nnn. | '%ING' | Character strings ending with ING, such as ING, BEING, and HAVING. |
Any match | %nnn% | Any portion of the character string contains nnn. | N'%A%' | Character strings containing A, such as A, ACT, CA, and TACT#2 |
Complete match | nnn | Character string is the same as nnn. | 'EQUAL' | EQUAL |
Partial match | _..._nnn_..._ | A specific portion of the character string is the same as nnn, but the remaining characters are different. | '_I_' | Three-letter character strings in which the second letter is I, such as BIT, HIT, and KIT. |
Repetition of at least once | mmm[0-9]+ or mmm[:DIGIT:]+ | The leading portion of the character string is mmm and the trailing portion is a numerical value. | 'KFPA11[0-9]+-E' or 'KFPA11[:DIGIT :]+-E' | Character strings beginning with KFPA11 in which numerics begin at the seventh character, followed by -E, such as KFPA11104-E and KFPA11901-E. |
Several selected characters | mmm(n|o) or mmm[no] | The leading portion of the character string is mmm and the ith character is n or o (i is any number). | 'KFPA%-(W|E)' or 'KFPA%-[WE]' | Character strings beginning with KFPA in which the last two characters are -W or -E, such as KFPA20008-W and KFPA11901-E. |
Repetition of between zero and one time | nnno?mmm | The leading and trailing portions of the character string are nnn and mmm, respectively, and the character W may or may not be present between the two character strings. | 'OW?N' | Character strings beginning with O and ending with N in which the character W may or may not be present between O and N, such as ON and OWN. |
Repetition of at least zero times | nnno*mmm | The leading and trailing portions of the character string are nnn and mmm, respectively, and the character 0 is repeated at least 0 times between the character strings. | 10*1 | Character strings beginning with 1, followed by 0 at least zero times, and ending with 1, such as 11, 101, and 1001. |
n repetitions | mmm{n} | The leading portion of the character string is mmm, followed by n repetitions. | [1-9]0{3} | Character strings beginning with 1 through 9, followed by 0 repeated three times, such as 1000, 2000, and 3000. |
Other | nnn%mmm | The leading and trailing portions of the character string are nnn and mmm, respectively. | 'O%N' | Character strings beginning with O and ending with N, such as ON, OWN, and ORIGIN. |
%nnn%mmm% | Character string containing nnn anywhere within it, and containing mmm anywhere in the following portion. | '%O%N%' | Character strings containing the character O and also containing the character N in the following portion, such as ON, ONE, DOWN, and COUNT. |
nnn_..._ mmm% | The leading portion of the character string is nnn and the trailing portion is mmm. | 'CO_ _ECT%' | Character strings beginning with CO and in which the fifth through seventh characters are ECT, such as CORRECT, CONNECTOR, and CONNECTION. |
- Note:
- nnn and mmm are any character strings that do not contain a special character.
- #1
- Because the space character is also used as a comparison character, comparison with data containing trailing spaces produces the FALSE result.
- #2
- Special characters appropriate to individual national characters are used for the special characters in a national character string.
- escape-character
- Special characters within a pattern character string cannot be handled as regular characters. When a special character needs to be specified as a regular character, an escape character must also be specified. The escape character is any character that you specify following the ESCAPE keyword. You can then specify the defined escape character before the special character in the pattern character string, which causes the special character to be handled as a regular character. You can specify as the escape character a character literal, ? parameter, embedded variable, SQL variable name, or SQL parameter name.
- Example 1
- A character string containing '5%', such as '5%' or '25%':
'%5\%%' ESCAPE '\'
- Example 2
- A character string ending with 'PRINT_REC', such as 'SQLPRINT_REC':
'%PRINT\_REC' ESCAPE '\'
- Example 3
- A hexadecimal character string containing X'48695244425f' in the binary string, such as X'48695244425f':
X'4869524442ee5f' ESCAPE X'ee'
- The following characters can be specified as escape characters:
Data type of item | Character that can be specified |
---|
Character string data (CHAR, VARCHAR) | Default character set | Any single character (single-byte character)#1 |
EBCDIK |
UTF16 | Any single character#2 |
Mixed character string data (MCHAR, MVARCHAR) | Any single character#2 |
National character data (NCHAR, NVARCHAR) | Any single character (double-byte character)#3 |
Binary data (BINARY) | Any single byte value |
- Note
- You must be sure to specify the special character following the escape character.
- #1
- All characters or data values are treated as single-byte characters.
- #2
- Data values that are not characters are treated as characters with the smallest length allowed for the character encoding (two bytes for UTF-16 and one byte for UTF-8 and SJIS).
- #3
- All characters or data values are treated as double-byte characters.
- Notes
- For improved performance, the defined length of the column used in a value expression on the left-hand side of SIMILAR should be either a maximum of 255 bytes (CHAR, VARCHAR, MCHAR, MVARCHAR, or BINARY) or a maximum of 127 characters (NCHAR or NVARCHAR).
- Multi-byte characters stored in a CHAR or VARCHAR-type column are evaluated byte by byte. Consequently, if the character code for a single-byte character specified in a pattern character string is included in the character codes for the multi-byte characters, the result of the SIMILAR predicate is true.
Example:
Execute the following query in a condition where the query is set up in sjis character codes, the CHAR-type column C1 is in Table T1, and the row
is in column C1:
SELECT C1 FROM T1 WHERE C1 LIKE '%A%' ;
The character code for the character
in hexadecimal is 8341. The character code in hexadecimal for the character A in the pattern character string is 41. Therefore, because the character code for
, which is a multi-byte character, includes the character code for the single-byte character A, the result of the SIMILAR predicate is true.
- If the pattern character string is extremely long or if the special characters {} are specified consecutively, search performance may deteriorate or the amount of memory used may increase.
(7) BETWEEN predicate
- Format
row-value-constructor-1 [NOT] BETWEEN
row-value-constructor-2 AND row-value-constructor-3
- Conditions under which predicate is TRUE
The BETWEEN predicate is TRUE for those rows that satisfy the following condition:
row-value-constructor-2
row-value-constructor-1
row-value-constructor-3
If NOT is specified, the BETWEEN predicate is TRUE for those rows that do not satisfy the above condition.
- Rules
- (row-value-constructor-1)
- A row value constructor element consisting solely of a value specification cannot be specified.
- When a repetition column is specified, a subscript must be specified. If a repetition column is specified with a subscript and its elements meet specified conditions, the BETWEEN predicate will be TRUE.
- ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the BETWEEN predicate will be TRUE. If the BETWEEN predicate is not TRUE and a condition specified for at least one element in the column is UNKNOWN, the BETWEEN predicate will be UNKNOWN. If the BETWEEN predicate is neither TRUE nor UNKNOWN, it will be FALSE.
- If a repetition column is specified with a subscript and the column contains no elements, the BETWEEN predicate will be UNKNOWN.
- (row-value-constructor-2 and row-value-constructor-3)
- Repetition columns cannot be specified.
- Common
- Row value constructor elements located in corresponding positions in row value constructors are treated as corresponding values. Corresponding values must have data types that are mutually convertible. If, however, national character data is specified in row-value-constructor-1, and character string literals are specified in the corresponding values for row-value-constructor-2 or row-value-constructor-3, the character string literals are treated as national character string literals. If a character string literal is treated as a national character string literal, HiRDB only checks the length of the character data, not the character code.
![[Figure]](figure/zu2s0117.gif)
- Values of any of the following data types cannot be specified in row-value-constructor-1, row-value-constructor-2, or row-value-constructor-3:
- BLOB
- BINARY with a minimum length of 32,001 bytes
- BOOLEAN
- Abstract data type
(8) Quantified predicate
- Format
row-value-constructor {= | <> | ^= | != | < | <=|> | >=}
{{ANY | SOME} | ALL}
(table-subquery)
([SQL-optimization-specification-for-subquery-execution-method]
SELECT [{ALL | DISTINCT}] {selection expression |*}
(table-expression)
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [,value-expression]...]
[HAVING search condition])
- Conditions under which a predicate is TRUE
If either ANY or SOME is specified and if any one row in the results of a table subquery satisfies the comparison condition with respect to a row value constructor, the result of the quantified predicate is TRUE.
If ALL is specified and if all rows in the results of a table subquery satisfy the comparison condition with respect to a row value constructor, or if the result of a table subquery is the empty set, the result of the quantified predicate is TRUE.
- Rules
- Values that yield any of the following data types as a result of an operation cannot be specified:
- BLOB
- BINARY with a minimum defined length of 32,001 bytes
- BOOLEAN
- Abstract data type
- This predicate is indefinite with respect to rows in which the result of the row value constructor is null.
- For table subqueries, see 2.4 Subqueries.
- The SOME quantified predicate and the ANY quantified predicate produce the same results.
- Some quantified predicates have the same meaning as the IN predicate, as follows:
Quantified predicate | IN predicate |
---|
row-value-constructor = row-value-constructor = ANY table-subquery or row-value-constructor = SOME table-subquery | row-value-constructor IN table-subquery |
row-value-constructor <> ALL table-subquery | row-value-constructor NOT IN table-subquery |
- The following table lists the results of a quantified predicate in which either ANY or SOME is specified. If any row in the results of a table subquery satisfies specified conditions, the result of the quantified predicate is TRUE. If the results of comparison of all rows are all FALSE or if the result of a table subquery is the empty set, the result of the quantified predicate is FALSE. If neither is the case, the result of the quantified predicate is indefinite.
Table 2-9 Result of a quantified predicate with ANY or SOME specified
Result of comparing rows in subquery | Result of quantified predicate (ANY or SOME) |
---|
TRUE rows found | TRUE |
No TRUE rows | Indefinite | Indefinite |
Not indefinite | FALSE |
Empty set | FALSE |
- The table below lists the results of a quantified predicate with ALL specified. The result of the quantified predicate is TRUE if the comparison results of all rows in the results of a table subquery are TRUE or if the results of a table subquery are TRUE. If neither of the above two conditions is applicable, the quantified predicate is indefinite.
Table 2-10 Result of a quantified predicate with ALL specified
Result of comparing rows in subquery | Result of quantified predicate (ALL) |
---|
FALSE rows found | FALSE |
No FALSE rows | Indefinite | Indefinite |
Not indefinite | TRUE |
Empty set | TRUE |
- A qualified predicate cannot be specified in the following locations:
- Search condition of an IF statement
- Search condition of a WHILE statement
- WHEN search condition (trigger action condition) of CREATE TRIGGER
- When specifying a repetition column in a row value constructor element, specify a subscript. If a repetition column is specified with a subscript and its elements meet specified conditions, the quantified predicate will be TRUE.
- ANY can be specified as a subscript for a repetition column. If ANY is specified and at least one element in the column meets specified conditions, the quantified predicate will be TRUE. If the quantified predicate is not TRUE and a condition specified for at least one element in the column is UNKNOWN, the quantified predicate will be UNKNOWN. If the quantified predicate is neither TRUE nor UNKNOWN, it will be FALSE.
- If a repetition column is specified with a subscript and the column contains no elements, the quantified predicate will be UNKNOWN.
- The number of columns in the row value constructor specified on the left side of a quantified predicate must have the same number of columns as the results of a table subquery.
- Note
- Specifying a quantified predicate may cause HiRDB to create a work table. In this case, the processing of the quantified predicate may be subject to restrictions, depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 9 Installation and Design Guide.
(9) EXISTS predicate
- Format
EXISTS
(table-subquery)
([SQL-optimization-specification-for-subquery-execution-method]
SELECT [{ALL|DISTINCT}] {selection-expression|*}
(Table-Expression)
FROM table-reference [,table-reference]...
[WHERE search-condition]]...
[GROUP BY value-expression [, value-expression]...]
[HAVING search-condition])
- Conditions under which a predicate is TRUE
The results of the EXISTS predicate are TRUE unless the results of the table subquery are the empty set.
- Rules
- For table subqueries, see 2.4 Subqueries.
- The EXISTS predicate is used to determine whether the results of a table subquery are the empty set.
- The following table lists the results of an EXISTS predicate. The result of the EXISTS predicate is TRUE if the results of a table subquery are one or more rows. The EXISTS predicate is FALSE if the result of the subquery is the empty set.
Table 2-11 Result of EXISTS predicate
Number of rows meeting query condition as result of subquery | Result of EXISTS predicate |
---|
One or more rows | TRUE |
0 rows | FALSE |
- The EXISTS predicate cannot be specified in the following locations:
- Search condition of an IF statement
- Search condition of a WHILE statement
- WHEN search condition (trigger action condition) of CREATE TRIGGER
(10) Boolean predicate
- Format
value-expression IS [NOT] {TRUE|FALSE|UNKNOWN}
- Conditions under which a predicate is TRUE
If the logical value of a value expression matches the specified TRUE, FALSE, or UNKNOWN, the Boolean predicate will be TRUE. If NOT is specified and the logical value of the value expression does not match the specified TRUE, FALSE, or UNKNOWN, the Boolean predicate will be TRUE.
- Rules
- The following table lists the results of a predicate obtained by evaluating a Boolean predicate. If NOT is specified, the logical values shown in Table 2-10 are reversed.
Table 2-12 Results of a predicate obtained by evaluating a Boolean predicate
Logical value of value expression | IS TRUE | IS FALSE | IS UNKNOWN |
---|
TRUE | TRUE | FALSE | FALSE |
FALSE | FALSE | TRUE | FALSE |
UNKNOWN | FALSE | FALSE | TRUE |
- Values in the following data type can be specified in a value expression:
- An undefined Boolean value is the same as a null value.
(11) Structured repetition predicate
- Format
ARRAY (column-specification [, column-specification]...)
[ANY] (search-condition)
- Conditions under which a predicate is TRUE
If the repetition column specified in ARRAY (column-specification [, column-specification]...) is treated as repetitions of multiple items that are a set of elements with the same subscript and if any of the elements meet specified search conditions, the structured repetition predicate will be TRUE.
- Rules
- ARRAY (column-specification [,column-specification]...)
- column-specification specifies a repetition column to be structured.
- The column specifications must be entirely from one index constituent column.
- The following cannot be specified in a column specification:
Columns of different tables
Columns derived from different tables
"Different tables" includes tables with the same base table but different correlation names.
- Columns that make external references cannot be specified.
- The same column cannot be specified more than once.
- A maximum of 16 columns can be specified.
- search-condition
- Specifies a search condition. Structured repetition predicates are subject to the following rules.
- None of the following items can be specified in a search condition:
Subscripted column specifications
Columns other than a repetition column specified in ARRAY (column-specification [, column-specification]...)
Predicates containing a system-defined scalar function, a function call, or IS_USER_CONTAINED_IN_HDS_GROUP
Structured repetition columns
XMLEXISTS predicate
Predicates not containing a column specification
Subqueries
- If the NULL predicate is specified and the column does not contain any elements, the NULL predicate will be UNKNOWN. If a specified element is the NULL value, the NULL predicate will be TRUE.
- Common rules
- Search conditions that contain a structured repetition predicate cannot be negated by NOT.
- Structured repetition predicates cannot be specified in a search condition in the IF or WHILE statement.
- Structured repetition predicates cannot be specified in the HAVING clause.
- Structured repetition predicates cannot be specified in a search condition in a CASE expression.
- An OR containing a structured repetition predicate and any of the following columns (excluding external reference columns) in its operand search condition cannot be specified:
- Columns of different tables
- Columns derived from different tables
"Different tables" includes tables with the same base table but different correlation names.
- Structured repetition predicates cannot be specified in a search condition in a derived query expression in a view definition.
- When a structured repetition predicate in the ON search condition for a query specification including an outer-joined joined table is specified, specify columns of the inner table in the column specification.
- When a structured repetition predicate in the WHERE clause of a query specification including an outer-joined joined table is specified, specify columns of the outermost table in the outer join in the column specification.
- Usage example
From a listing of students' grades, find the names of students who had a minimum score of 85 in mathematics. The grades list consists of a repetition column of 10 elements composed of subjects and scores.
SELECT name FROM grades-list
WHERE ARRAY (subject,score) [ANY]
(subject='mathematics' AND score>=85)
![[Figure]](figure/zu2s0111.gif)
- Note
- The grades list has the following table definition and index definition:
- Table definition:
CREATE TABLE grades-list (name MCHAR(10),
subject MCHAR(10) ARRAY[4],
score SMALLINT ARRAY[4];
- Index definition:
CREAT INDEX subject-score
ON grades-list (subject, score);
(12) XMLEXISTS predicate