Conversion rules for comparisons
When two strings are compared, one of the strings is first converted, if necessary, to the coded character set of the other string. Conversion is necessary only if certain rules apply.
Conversion is necessary only if all of the following are true:
- The CCSIDs of the two strings are different.
- Neither CCSID is X'FFFF' (neither string is defined as a binary string).
- The string selected for conversion is neither null nor empty.
- The following conversion tables (Table 2 or Table 3) indicate when conversion is necessary.
Type of operand | CCSID of the operand type |
---|---|
Columns | CCSID from the containing table |
String constants | CCSID associated with the application encoding scheme. For dynamic statements, this is the CURRENT APPLICATION ENCODING SCHEME special register. For static statements, this is the ENCODING bind option or the APPLICATION ENCODING SCHEME option of the CREATE PROCEDURE or ALTER PROCEDURE statement for native SQL procedures.. |
Special registers | CCSID associated with the application encoding scheme. For dynamic statements, this is the CURRENT APPLICATION ENCODING SCHEME special register. For static statements, this is the ENCODING bind option or the APPLICATION ENCODING SCHEME option of the CREATE PROCEDURE or ALTER PROCEDURE statement for native SQL procedures. |
Host variables | CCSID specified in the DECLARE VARIABLE statement, associated with the application encoding scheme, or specified in SQLDAID or SQLDA |
Derived value based on a column | CCSID
derived from the source of the derived value. A derived value based
on a column is an expression whose source is directly or indirectly
based on columns. The CCSID of such an expression is the CCSID derived
from its source. For example:
|
Derived value not based on a column | CCSID
derived from the source of the derived value. A derived value not based
on a column is an expression whose source is not directly or indirectly
based on any column. The CCSID of such an expression is the CCSID
derived from its source.
|
The following table shows which operand supplies the target CCSID set when the comparison is part of an SQL statement involving multiple tables with different CCSID sets.
First operand | Second operand | |||||
---|---|---|---|---|---|---|
Column value | String constant | Special register | Host variable | Derived value based on a column | Derived value not based on a column | |
Column value | 1 | first operand | first operand | first operand, 2 | 1 | first operand |
String constant | second operand | 1 | 1 | 1 | second operand | 1 |
Special register | second operand | 1 | 1 | 1 | second operand | 1 |
Host variable | second operand | 1 | 1 | 1 | second operand, 2 | 1 |
Derived value based on a column | 1 | first operand | first operand | first operand, 2 | 1 | first operand |
Derived value not based on a column | second operand | 1 | 1 | 1 | second operand | 1 |
Note:
|
First operand | Second operand | ||
---|---|---|---|
SBCS data | Mixed data | DBCS data | |
SBCS data | second operand1 | second operand | |
Mixed data | first operand1 | second operand | |
DBCS data | first operand | first operand | |
Note:
|
string-constant-SBCS =derived-value-not-based-on-column-DBCS
Assume
that the operands have different encoding schemes. First look at Table 2. The
relevant table entry is in the third row and second column. The value
for this entry shows that if the CCSID sets are different, the operands
are converted to Unicode. The first operand (string-constant-SBCS)
is converted to UTF-8 (Mixed) if it is not already Unicode. In addition,
the second operand (derived-value-not-based-on-column-DBCS) is converted
to UTF-16 (Unicode DBCS) if necessary. After the operands have been converted
to Unicode, Table 3 is
used to determine which operand supplies the specific CCSID for the
conversion. The relevant table entry is in the second row and third
column. It indicates that the second operand (derived-value-not-based-on-column-DBCS)
determines the CCSID because DBCS data takes precedence over Mixed
data.An error occurs if a character of the string cannot be converted, the SYSSTRINGS table is used but contains no information about the pair of CCSIDs of the operands being compared, or DB2 cannot do the conversion through z/OS® support for Unicode. A warning occurs if a character of the string is converted to a substitution character.
A derived value based on a column is an expression that includes columns that affects the result CCSID of the expression. For example, in the expression COL1||'abc', COL1 determines the result CCSID. Therefore, the expression COL1||'abc' is considered to be a derived value based on a column that continues to give the column precedence in any further comparisons. The expression CASE WHEN COL1 > 1 THEN 'abc' ELSE 'def' END contains a column that does not affect the result CCSID of the expression and is therefore not considered to be a derived value based on a column.
Expression | Condition |
---|---|
expression1 || expression2 | expression1 or expression2 is a column or a derived value based on a column |
CASE when-clause THEN result-expression ELSE result-expression END | any result-expression is a string-expression that is a column or derived value based on a column |
CAST(expression as data-type) | expression is a string-expression that is a column or a derived value based on a column and data-type is a string data type |
Scalar-fullselect: (SELECT expression FROM table) | expression is a string-expression that is a column or a derived value based on a column and data-type is a string data type |
From CCSID | To CCSID | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
EBCDIC | ASCII | Unicode | ||||||||
SBCS | Mixed | DBCS | SBCS | Mixed | DBCS | SBCS | UTF-8 | UTF-16 | ||
EBCDIC | SBCS | X | X | X*21 | X | X | X*21 | X1 | X*3 | X*2 |
Mixed | X | X | X*21 | X | X | X*21 | X1 | X*3 | X*2 | |
DBCS | X*0.51 | X+2 | X | X*0.51 | X | X | X*0.5 | X*1.5 | X | |
ASCII | SBCS | X | X | X*21 | X | X | X*21 | X1 | X*3 | X*2 |
Mixed | X | X*1.8 | X*21 | X | X | X*21 | X1 | X*3 | X*2 | |
DBCS | X*0.51 | X+2 | X | X*0.51 | X | X | X*0.5 | X*1.5 | X | |
Unicode | SBCS | X | X | X*2 | X | X | X*2 | X | X | X*2 |
UTF-8 | X | X*1.25 | X | X | X | X | X | X | X*2 | |
UTF-16 | X*0.5 | X+2 | X | X*0.5 | X | X | X*0.5 | X*1.5 | X | |
Note:
|