Re: Get Count of Decimal Places

On Mar 13, 12:55 pm, webtourist <webtour..._at_gmail.com> wrote:
> I have some number data in table like this:
>
> .050485436893203883495145631
> 234.040388349514563106796116505
>
> i.e. very large decimal places..
>
> I'd like to update them and ROUND them all to 8th decimal.
>
> How to get the number of decimal places ? or I have to do this via
> to_char, substr etc....
>
> thanks

Why do you 'need' to know the number of decimal places? It doesn't matter, really:

update mytable
set mycol = round(mycol, 8);

To illustrate:

SQL> create table roundtst(
  2 mynum number(38,34)
  3 );

Table created.

SQL>
SQL> insert all
  2 into roundtst
  3 values (.050485436893203883495145631)   4 into roundtst
  5 values (234.040388349514563106796116505)   6 select * from dual;

2 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select round(mynum, 8) from roundtst;

      ROUND(MYNUM,8)

           .05048544
        234.04038835

SQL>
SQL> update roundtst
  2 set mynum=round(mynum,8);

2 rows updated.

SQL>
SQL> select *
  2 from roundtst;

               MYNUM

           .05048544
        234.04038835

ROUND() returns the specified number of decimal places, rounding the result to that precision. It matters not how many decimal places there are, so you needn't know that information. Of course you can return that information if you're so inclined:

SQL> select
  2 length(mynum),
  3 instr(mynum, '.'),
  4 length(mynum)-instr(mynum, '.') num_decimals   5 from roundtst;

       LENGTH(MYNUM) INSTR(MYNUM,'.') NUM_DECIMALS

-------------------- -------------------- --------------------
                  28                    1                   27
                  31                    4                   27

SQL> But as I said before such 'knowledge' is unnecessary.

David Fitzjarrell Received on Thu Mar 13 2008 - 13:25:54 CDT