SQL Pocket Guide, 3rd Edition

Numeric Conversions: DB2

Use the following functions to convert between different numeric types or between numeric and text types:

BIGINT(numeric)
BIGINT(character)
CHAR(integer)
CHAR(decimal [,decimal_character])
CHAR(floating [,decimal_character])
DECFLOAT(numeric, 16or34)
DECFLOAT(character, 16or34
        [, decimal_character])
DECIMAL(numeric [,precision[,scale]])
DECIMAL(character [,precision[,scale
                  [,decimal_character]]])
DOUBLE(numeric)
DOUBLE(character)
DOUBLE_PRECISION(numeric)
FLOAT(numeric)
REAL(numeric)
SMALLINT(numeric)
SMALLINT(character)

See Datetime Conversions: DB2 for information on converting between dates and numbers.

In the syntax, numeric can be any numeric type or expression; character can be any fixed- or variable-length character type or expression; integer can be any integer type or expression; and decimal can be any decimal type or expression.

Note

DB2 also provides compatibility with TO_CHAR and TO_NUMBER, as implemented by Oracle. See the section Numeric Conversions: Oracle for details.

Each function converts its argument to the type indicated by the function name. The following example shows DECFLOAT being used to convert from a character string:

SELECT DECFLOAT('100.123451234512345',16)
FROM dual;

100.1234512345123

And following is an example showing CHAR and DECIMAL being used to convert back and forth between numbers and strings:

SELECT CHAR(100.12345),
       CHAR(DECIMAL('100.12345',5,2))
FROM pivot WHERE x=1;

100.12345  100.12

DECIMAL’s default scale is zero when converting ...