The DECIMAL function returns a decimal representation.
Numeric to Decimal

>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'
>--(--numeric-expression--+----------------------------+--)----><
'-,--precision--+----------+-'
'-,--scale-'
String to Decimal

>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'
>--(--string-expression--+------------------------------------------------------+--)-><
'-,--precision--+------------------------------------+-'
'-,--scale--+----------------------+-'
'-,--decimal-character-'
The DECIMAL function returns a decimal representation
of:
- A number
- A character or graphic string representation of a decimal number
- A character or graphic string representation of an integer
- A character or graphic string representation of a floating-point
number
- A character or graphic string representation of a decimal floating-point
number
Numeric to Decimal
- numeric-expression
- An expression that returns a value of any built-in numeric data
type.
- precision
- An integer constant with a value greater than or equal to 1 and
less than or equal to 63.
The default for precision depends
on the data type of the numeric-expression:
- 5 for small integer
- 11 for large integer
- 19 for big integer
- 15 for floating point, decimal, numeric, or nonzero scale binary
31 for decimal floating point
- scale
- An integer constant that is greater than or equal to 0 and less
than or equal to precision. If not specified,
the default is 0.
The result is the same number that would occur if the
first argument were assigned to a decimal column or variable with
a precision of precision and a scale of scale. An error
is returned if the number of significant decimal digits required to
represent the whole part of the number is greater than precision-scale.
If the first argument can be null, the result can be null; if the
first argument is null, the result is the null value.
String to Decimal
- string-expression
- An expression that returns a character-string or graphic-string
representation of a number. Leading and trailing blanks are eliminated
and the resulting string must conform to the rules for forming a floating-point,
decimal floating-point, integer, or decimal constant.
- precision
- An integer constant that is greater than or equal to 1 and less
than or equal to 63. If not specified, the default is 15.
- scale
- An integer constant that is greater than or equal to 0 and less
than or equal to precision. If not specified,
the default is 0.
- decimal-character
- Specifies the single-byte character constant that is used to delimit
the decimal digits in string-expression from
the whole part of the number. The character must be a period or comma.
If decimal-character is not specified, the
decimal point is the default decimal separator character. For more
information, see Decimal point.
The result is the same number that would result from CAST(string-expression AS
DECIMAL(precision,scale)).
Digits are truncated from the end of the decimal number if the number
of digits to the right of the decimal separator character is greater
than scale. An error is returned if the
number of significant digits to the left of the decimal character
(the whole part of the number) in string-expression is
greater than precision-scale. The default
decimal character is not valid in the substring if the decimal-character argument
is specified.
The result of the function is a decimal number with precision
of precision and scale of scale.
If the first argument can be null, the result can be null; if the
first argument is null, the result is the null value.
Note
Syntax alternatives: The CAST
specification should be used to increase the portability of applications
when precision is specified. For more information, see CAST specification.
Examples
- Use the DECIMAL function in order to force a DECIMAL data type
(with a precision of 5 and a scale of 2) to be returned in a select-list
for the EDLEVEL column (data type = SMALLINT) in the EMPLOYEE table.
The EMPNO column should also appear in the select list.
SELECT EMPNO, DECIMAL(EDLEVEL,5,2)
FROM EMPLOYEE
- Using the PROJECT table, select all of the starting dates (PRSTDATE)
that have been incremented by a duration that is specified in a host
variable. Assume the host variable PERIOD is of type INTEGER. Then,
in order to use its value as a date duration it must be “cast” as
DECIMAL(8,0).
SELECT PRSTDATE + DECIMAL(:PERIOD,8)
FROM PROJECT
- Assume that updates to the SALARY column are input through a window
as a character string using comma as a decimal character (for example,
the user inputs 21400,50). Once validated by the application, it is
assigned to the host variable newsalary which is defined as CHAR(10).
UPDATE STAFF
SET SALARY = DECIMAL(:newsalary, 9, 2, ',')
WHERE ID = :empid
The value
of SALARY becomes 21400.50.