SQL Reference

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 section 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

• Arguments of the scalar function COALESCE (and VALUE)

• 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 coded character set 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. (14)

• Otherwise, the result code page is determined by Table 6. An entry of 'first' in the table means the code page from the first operand is selected and an entry of 'second' means the code page from the second operand is selected.
  

  Table 6. Selecting the Code Page of the Intermediate Result
  

  First Operand	Second Operand
  	Column Value	Derived Value	Constant	Special Register	Host Variable
  Column Value	first	first	first	first	first
  Derived Value	second	first	first	first	first
  Constant	second	second	first	first	first
  Special Register	second	second	first	first	first
  Host Variable	second	second	second	second	first

An intermediate result is considered to be a derived value operand. An expression that is not a single column value, constant, special register, or host variable is also considered a derived value operand. There is an exception to this if the expression is a CAST specification (or a call to a function that is equivalent). In this case, the kind for the first operand is based on the first argument of the CAST specification.

View columns are considered to have the operand type of the object on which they are ultimately based. For example, a view column defined on a table column is considered to be a column value, whereas a view column based on a string expression (for example, A CONCAT B) is considered to be a derived value.

Conversions to the coded character set of the result are performed, if necessary, for:

• An operand of the concatenation operator
• The selected argument of the COALESCE (or VALUE) scalar function
• The selected result expression of the CASE expression
• 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
• The code page conversion selection table indicates that conversion is necessary.

Examples

Example 1:  Given the following:

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, since the dominant operand is the column COL_1.

Example 2:  Using the 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 the code page 850.

[ DB2 List of Books | Search the DB2 Books ]