DECIMAL or DEC

The DECIMAL function returns a decimal representation of either a number or a character-string or graphic-string representation of a number, an integer, or a decimal number.

Numeric to Decimal:

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

String to Decimal:

>>-+-DECIMAL-+-(string-expression-+----------------------------------------------+-)-><
   '-DEC-----'                    '-,precision-+-------------------------------+-'     
                                               '-,scale-+--------------------+-'       
                                                        '-,decimal-character-'         

The schema is SYSIBM.

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 in the range of 1 to 31. The value of this second argument specifies the precision of the result.

The default value depends on the data type of the first argument as follows:

  • 5 if the first argument is a small integer
  • 11 if the first argument is a large integer
  • 19 if the first argument is a big integer
  • 31 if the first argument is a DECFLOAT value
  • 15 in all other cases
scale
An integer constant that is greater than or equal to zero and less than or equal to precision. The value specifies the scale of the result. The default value is 0.

The result of the function is the same number that would occur if the argument were assigned to a decimal column or variable with precision p and scale s, where p and s are specified by the second and third arguments. An error occurs if the number of significant digits required to represent the whole part of the number is greater than p-s.

String to decimal

string-expression
An expression that returns a value of a character or graphic string (except a CLOB or DBCLOB) with a length attribute that is not greater than 255 bytes. The string must contain a valid string representation of a number. Leading and trailing blanks are removed from the string, and the resulting substring must conform to the rules for forming a valid string representation of an SQL integer or decimal constant.
precision
An integer constant with a value in the range of 1 to 31. The value of this second argument specifies the precision of the result.

The default value depends on the data type of the first argument as follows:

  • 5 if the first argument is a small integer
  • 11 if the first argument is a large integer
  • 15 in all other cases
scale
An integer constant that is greater than or equal to zero and less than or equal to precision. The value specifies the scale of the result. The default value is 0.
decimal-character
A single-byte character constant used to delimit the decimal digits in string-expression from the whole part of the number. The character cannot be a digit, plus (+), minus (-), or blank. The default value is period (.) or comma (,); the default value cannot be used in string-expression if a different value for decimal-character is specified.

The result is the same number that would result from CAST(string-expression AS DECIMAL(p,s)). 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 the scale s. 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 p-s.

The result of the function is a decimal number with precision of p and scale of s, where p and s are the second and third arguments. If the first argument can be null, the result can be null; if the first argument is null, the result is null.

Note: To increase the portability of applications when the precision is specified, use the CAST specification. For more information, see CAST specification.
Example 1: Represent the average salary of the employees in DSN8A10.EMP as an 8-digit decimal number with two of these digits to the right of the decimal point. 
   SELECT DECIMAL(AVG(SALARY),8,2)
     FROM DSN8A10.EMP;
Example 2: Assume that updates to the SALARY column are input as a character string that uses comma as the decimal character. For example, the user inputs 21400,50. The input value is assigned to the host variable NEWSALARY that is defined as CHAR(10), and the host variable is used in the following UPDATE statement: 
   UPDATE DSN8A10.EMP
     SET SALARY = DECIMAL (:NEWSALARY,9,2,',')
     WHERE EMPNO = :EMPID;