How to Use DIGITS function to get Digits from Decimal Column Value

DIGITS ( expression )

The schema is SYSIBM. The DIGITS function returns a character string representation of a number.

The argument must be an expression that returns a value of type SMALLINT, INTEGER, BIGINT or DECIMAL.

“If the argument can be null, the result can be null; if the argument is null, the result is the null value.”

The result of the function is a fixed-length character string representing the absolute value of the argument without regard to its scale.

The result does not include a sign or a decimal character. Instead, it consists exclusively of digits, including, if necessary, leading zeros to fill out the string. The length of the
string is:

  • 5 if the argument is a small integer
  • 10 if the argument is a large integer
  • 19 if the argument is a big integer
  • p if the argument is a decimal number with a precision of p.

Tuning of PL/SQL – A well designed online Training: Highly useful who wish to learn to tune of PL/SQL….a worthy course.

How to use DIGITS function


Assume that a table called TABLEX contains an INTEGER column called INTCOL containing 10-digit numbers. List all distinct four-digit combinations of the first four digits contained in column INTCOL.


Assume that COLUMNX has the DECIMAL(6,2) data type and that one of its values is -6.28. Then, for this value:

DIGITS(COLUMNX) …….returns the value ‘000628’.


The result is a string of length six (the precision of the column) with leading zeros padding the string out to this length. Neither sign nor decimal point appears in the result.

Also read:

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.