Results 1 to 9 of 9
Thread: 'DIGITS' function in Oracle

021207, 07:03 #1Registered User
 Join Date
 Oct 2006
 Posts
 83
Unanswered: 'DIGITS' function in Oracle
Hi all, Im having a DB2 query which is below. I need the equivalent of it in ORACLE.
I came to know that there is no direct equivalent for DIGITS function in Oracle. I have wasted a lot of time in finding out this.
Immediate help will be highly helpful
SELECT
CASE
WHEN MAX(i) IS NULL THEN
'02'
ELSE SUBSTR(DIGITS(MAX(CAST( j AS INTEGER ))+1), 8,3)
END
FROM
raj WHERE k = 2;

021207, 07:19 #2Registered User
 Join Date
 Nov 2003
 Posts
 2,933
Provided Answers: 12Could you tell those people who have never used DB2 what the digits function does?

021207, 08:10 #3Registered User
 Join Date
 Oct 2006
 Posts
 83
Kindly refer the below URL for details on DIGITS function
http://publib.boulder.ibm.com/infoce...n/r0000927.htm

021207, 08:13 #4Registered User
 Join Date
 Oct 2006
 Posts
 83
DIGITS scalar function
Read syntax diagramSkip visual syntax diagram>>DIGITS(expression)><
The schema is SYSIBM.
The DIGITS function returns a characterstring 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 fixedlength 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 10digit 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'.

021207, 08:43 #5Registered User
 Join Date
 Nov 2003
 Posts
 2,933
Provided Answers: 12This sounds like you could use the to_char() function.
This will work fine with nondecimal numbers.
To "get rid" of the decimal symbol you probably need to apply some logic (e.g. multiplying the value with a constant and remove trailing zeros or something like that).

021207, 09:31 #6Registered User
 Join Date
 Oct 2006
 Posts
 83
But DIGITS function will trail some ZERO's to the given value based on the type(INteger,longint etc....), where as to_char() doesn't have such property.
For ex im having
select DIGITS(CAST( 2 as INTEGER )) from SYSIBM.SYSDUMMY1 in DB2 will return 0000000003
but SELECT to_char(CAST(2 as INTEGER)) FROM SYS.DUAL will return 2

021207, 09:44 #7Registered User
 Join Date
 Nov 2003
 Posts
 2,933
Provided Answers: 12With the correct format model you can get leading zeros
(you did mean leading, not trailing zeros, right?)
Code:select to_char(2, 'FM0000000000') from dual

021207, 09:46 #8Registered User
 Join Date
 Oct 2006
 Posts
 83
Is there any way to increase the length of the output of the 'to_char()' function to 10

021207, 10:05 #9Registered User
 Join Date
 Nov 2003
 Posts
 2,933
Provided Answers: 12Read the link I supplied. Everything is in there.
(Hint: Count the number of zeros in the format string)