If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > 'DIGITS' function in Oracle

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2006
Posts: 83
Thumbs down '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;
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,807
Could you tell those people who have never used DB2 what the digits function does?
Reply With Quote
  #3 (permalink)  
Old
Registered 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
Reply With Quote
  #4 (permalink)  
Old
Registered 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 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'.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,807
This sounds like you could use the to_char() function.

This will work fine with non-decimal 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).
Reply With Quote
  #6 (permalink)  
Old
Registered 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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,807
With 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
will return 0000000002
Reply With Quote
  #8 (permalink)  
Old
Registered 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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,807
Read the link I supplied. Everything is in there.
(Hint: Count the number of zeros in the format string)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On