In SQL, the DIGITS function extracts digits from a decimal value, which ignores sign, periods. It gives pure numeric digits. The shared example gives you a clear idea of how it works.
The syntax for DIGITS Function.
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.
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 appears in the result.
Keep Reading
Srinimf 