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 tuning of PL/SQL….a worthy course.
Examples:
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.
SELECT DISTINCT SUBSTR(DIGITS(INTCOL),1,4) FROM TABLEX
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’.
Result:
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 appear in the result.
Also read:
