CODEWITHSUNDEEP
oracle-database
Minku Jha
Minku Jha

Reputation: 17

custom round logic off in SQL

Iwant to round of the value upto 2 decimal point when third decimal digit is greater then 5:

 39.956 should be round off to 39.96,
 35.665 should be round off to 35.66 ,
 39.997 should be round off to 40.00 ,
 56.684 should be round off to 56.68.

I am trying to do below 

SELECT   CAST(FLOOR(VALUE) AS VARCHAR2(30))
   + CASE
         WHEN CAST(SUBSTR(SUBSTR(VALUE, INSTR(VALUE, '.')), 4) AS INT) > 5
         THEN
             CONCAT(
                    '.',
                    (  SUBSTR(
                              SUBSTR(VALUE, INSTR(VALUE, '.')),
                              2,
                              2
                             )
                     + 1)
                   )
         ELSE
             CONCAT(
                    '.',
                    SUBSTR(
                           SUBSTR(VALUE, INSTR(VALUE, '.')),
                           2,
                           2
                          )
                   )
     END
FROM DUAL;

but for the border cases, for example 39.897 and 39.997 it is not working.

Upvotes: 1

Views: 353

Answers (2)

user5683823
user5683823

Reputation:

Aleksej's solution will work fine and is probably the most efficient if it is known beforehand that the input numbers have at most three decimal places.

The problem can be generalized though, like so: round 38.445 down to 38.44; however, round 38.44503 to 38.45. (That is, if there are non-zero digits after the "5" in the third decimal position, then round up.)

Something like the query below can be used in the general case. The only time the result is different from "usual" rounding is when the input number has exactly three non-zero decimal places, and the third decimal place is 5. This is exactly how the solution reads.

with inp (n) as (select 38.445 from dual union all select 38.44503 from dual)
select n,
       round(n,2) - case when n = round(n, 3) and mod(1000*n, 10) = 5 
                         then 0.01 
                         else 0    end  as custom_rounded
from   inp;



         N CUSTOM_ROUNDED
---------- --------------
    38.445          38.44
  38.44503          38.45

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

Maybe you simply need this:

SQL> with test(num) as (
  2      select 39.956 from dual union all
  3      select 35.665 from dual union all
  4      select 39.997 from dual union all
  5      select 56.684 from dual
  6  )
  7  select num, round(num -0.001, 2)
  8  from test;

       NUM ROUND(NUM-0.001,2)
---------- ------------------
    39,956              39,96
    35,665              35,66
    39,997                 40
    56,684              56,68

Upvotes: 2

Related Questions