Rules for string conversions
The code page used to perform an operation is determined by rules which are applied to the operands in that operation. This topic explains those rules.
These rules apply to:
- Corresponding string columns in fullselects with set operations (UNION, INTERSECT and EXCEPT)
- Operands of concatenation
- Operands of predicates (with the exception of LIKE)
- Result expressions of a CASE expression and the DECODE scalar function
- Arguments of the scalar function COALESCE (also NVL and VALUE)
- Arguments of the scalar functions GREATEST, LEAST, MAX, and MIN
- The source-string and insert-string arguments of the scalar function OVERLAY (and INSERT)
- Expression values of the in list of an IN predicate
- Corresponding expressions of a multiple row VALUES clause.
In each case, the code page of the result is determined at bind time, and the execution of the operation may involve conversion of strings to the code page identified by that code page. A character that has no valid conversion is mapped to the substitution character for the character set and SQLWARN10 is set to 'W' in the SQLCA.
The code page of the result is determined by the code pages of the operands. The code pages of
the first two operands determine an intermediate result code page, this code page and the code page
of the next operand determine a new intermediate result code page (if applicable), and so on. The
last intermediate result code page and the code page of the last operand determine the code page of
the result string or column. For each pair of code pages, the result is determined by the sequential
application of the following rules:
- If the code pages are equal, the result is that code page.
- If either code page is BIT DATA (code page 0), the result code page is BIT DATA.
- In a Unicode database, if one code page denotes data in an encoding scheme that is different from the other code page, the result is UCS-2 over UTF-8 (that is, the graphic data type over the character data type). (In a non-Unicode database, conversion between different encoding schemes is not supported.)
- If both code pages are Unicode, and if one code page denotes data in an encoding scheme that is different from the other code page, the result is UTF-16 BE over UTF-8 (that is, the graphic data type over the character data type).
- If one code page is Unicode and the other is neither Unicode nor BIT DATA, the result is Unicode with the same encoding as the Unicode operand (either UTF-16 BE or UTF-8).
- For operands that are host variables (whose code page is not BIT DATA), the result code page is the database code page. Input data from such host variables is converted from the application code page to the database code page before being used.
In a non-Unicode database, if the result code page is Unicode, then the result collation is the alternate collating sequence as defined by the ALT_COLLATE database configuration parameter.
Conversions to the code page of the result are performed, if necessary, for:
- An operand of the concatenation operator
- The selected argument of the COALESCE (also NVL and VALUE) scalar function
- The selected argument of the scalar functions GREATEST, LEAST, MAX, and MIN
- The source-string and insert-string arguments of the scalar function OVERLAY (and INSERT)
- The selected result expression of the CASE expression and the DECODE scalar function
- The expressions of the in list of the IN predicate
- The corresponding expressions of a multiple row VALUES clause
- The corresponding columns involved in set operations.
Character conversion is necessary if all of the following are true:
- The code pages are different
- Neither string is BIT DATA
- The string is neither null nor empty
Examples
Example 1: Given the following in a database created with code page 850:
Expression | Type | Code Page |
---|---|---|
COL_1 | column | 850 |
HV_2 | host variable | 437 |
When evaluating the predicate:
COL_1 CONCAT :HV_2
the result
code page of the two operands is 850, because the host variable data will be converted to the
database code page before being used.Example 2: Using information from the previous example when evaluating the predicate:
COALESCE(COL_1, :HV_2:NULLIND,)
the result code page is 850;
therefore, the result code page for the COALESCE scalar function will be code page 850.