Results 1 to 9 of 9
  1. #1
    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;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,817
    Could you tell those people who have never used DB2 what the digits function does?

  3. #3
    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

  4. #4
    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'.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,817
    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).

  6. #6
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    2,817
    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

  8. #8
    Join Date
    Oct 2006
    Posts
    83
    Is there any way to increase the length of the output of the 'to_char()' function to 10

  9. #9
    Join Date
    Nov 2003
    Posts
    2,817
    Read the link I supplied. Everything is in there.
    (Hint: Count the number of zeros in the format string)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •