Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Jerusalem
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to do the following in Oracle SQL:
    Format a number as 4 d.p. but if the number has more than 4 numbers after the decimal point format it as 6 d.p.

    This is the code I would use to do 4 d.p.
    [sql]SELECT TRIM (TO_CHAR (4.5, '999D9999')) FROM DUAL[/sql]
    This is the code I would use to do 6 d.p.
    [sql]SELECT TRIM (TO_CHAR (4.5, '999D999999')) FROM DUAL[/sql]

    However I do not have an easy way to do this combination formatting. Does anyone have any ideas?

    Here is an example of the intended output.
    Format 4.5 as 4.5000
    Format 4.51 as 4.5100
    Format 4.512 as 4.5120
    Format 4.5121 as 4.5121
    Format 4.51211 as 4.512110
    Format 4.512112 as 4.512112
    Format 4.5121121 as 4.512112
    Format 4.51211212 as 4.512112
    etc.

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Jerusalem
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have solved this with the following Oracle P/SQL function

    [sql]FUNCTION roundTo4Or6DP(i_source IN VARCHAR2)
    RETURN VARCHAR2
    IS BEGIN
    IF (round(i_source,4)=round(i_source,6)) THEN
    RETURN trim(to_char(round(i_source,4), '999D9999'));
    ELSE
    RETURN trim(to_char(round(i_source,6), '999D999999'));
    END IF;
    END roundTo4Or6DP;[/sql]

    and I can then call this as...
    [sql]select roundTo4Or6DP('4.512222221') from dual[/sql]

    However I would still like to know if there is a neater way....

Posting Permissions

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