Implicit cast from string data to numeric data

When DB2® implicitly casts a character string or graphic string value to a numeric value, the target type is DECFLOAT(34) which is compatible with other numeric data types.

When GRAPHIC or VARGRAPHIC data types are involved, the encoding scheme must be UNICODE. The following table shows the target type and length:

Implicit conversion from a string value to a numeric value can happen during:

• Assignment (where the source value is a character string or graphic string and the target operand is a numeric data type).
• Comparisons
  When a character string or graphic string value is compared with a numeric value, DB2 implicitly converts the string value to DECFLOAT(34) and applies numeric comparison rule between the DECFLOAT(34) value and the other numeric value.

  • Basic predicates, quantified predicates, and DISTINCT predicates (one operand is numeric value and the other operand is character string or graphic string value)

    Numeric is the dominant data type. The character or graphic string value is cast to DECFLOAT(34) value.

  • BETWEEN predicates

    Numeric is the dominant data type. If any of the three operands is a numeric value, DB2 implicitly casts the character or graphic string operands to DECFLOAT data type.

  • IN predicates

    Numeric is the dominant data type. If any of the operands is a numeric value, DB2 implicitly casts the character or graphic string operands to DECFLOAT data type.

  • Searched-when-clause of CASE expression

    Pair-wise comparison is performed. Implicit cast of each pair follows the same rule as for a basic predicate. Implicit string and numeric cast is supported on searched-when-clause of CASE expression.

  • Search conditions in SQL control statements (one operand is numeric value and the other operand is character string or graphic string value)

    The search condition can appear in SQL control statements like the CASE statement, the IF statement, the REPEAT statement, and the WHILE statement. For comparisons in the search condition, numeric is the dominant data type. The character string or graphic string value is cast to a DECFLOAT(34) value. Implicit string and numeric cast is supported on the searched-when-clause of the CASE statement.

• Arithmetic operators (unary arithmetic operators + and - and infix arithmetic operators +, -, *, and /)

  If the operand of unary arithmetic operators is of a character string or graphic string data type, that operand is implicitly cast to DECFLOAT(34). For infix arithmetic operators, if one operand is a numeric value or both operands are character or graphic string values, DB2 implicitly casts the character string or graphic string operand to the DECFLOAT data type.

Implicit conversion is not supported in the following cases:

• One operand of a set operator is a string value and the other operand is a numeric value
• A string value is compared to or assigned to a numeric value that is the result of a fullselect which included a set operator
• For FULL OUTER JOIN when the join condition is between string and numeric data types