CODEWITHSUNDEEP
sqloracle-database
Ganesh
Ganesh

Reputation: 506

How to trim the large number(130 digit) to 40 digit in oracle

I want to trim the number to 40 digit, but getting below error:

Query:

select 1123123211231231231231231231231231231123123123123123123123213213123213123213123123213123123123123213123123123126666666355555899 from dual;

Error:

ORA-01426: numeric overflow
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.
Error at Line: 14 Column: 8

Upvotes: 0

Views: 222

Answers (2)

MT0
MT0

Reputation: 168291

Use a string and TO_NUMBER( value DEFAULT NULL ON CONVERSION ERROR ):

SELECT TO_NUMBER( value DEFAULT NULL ON CONVERSION ERROR )
FROM   long_numbers

Which, for the sample data:

CREATE TABLE long_numbers ( value ) AS
SELECT '1123123211231231231231231231231231231123123123123123123123213213123213123213123123213123123123123213123123123126666666355555899' FROM DUAL UNION ALL
SELECT '112312321123123123123123123123123123112312312312312312312321321312321312321312312321312312312312321312312312312666666635555589' FROM DUAL UNION ALL
SELECT '0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012345678901234567890123456789012345678901234567890' FROM DUAL;

Outputs:

|                                                                                                          TO_NUMBER(VALUEDEFAULTNULLONCONVERSIONERROR) |
| ----------------------------------------------------------------------------------------------------------------------------------------------------: |
|                                                                                                                                                  null |
|                        112312321123123123123123123123123123112300000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| .0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000123456789012345678901234567890123456789 |

Why doesn't the first value work?

From the Oracle data types documentation:

The following numbers can be stored in a NUMBER column:

  • Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
  • Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits
  • Zero

So your first example will not work as it is outside the limits that the NUMBER data type can accept. There is nothing that can be done to display it as a NUMBER as it is too big.

What can I use instead of a NUMBER data type?

If you require the exact value then you will need to store it as a string.

If you want an approximate numeric value then you can store it as a BINARY_DOUBLE:

SELECT TO_BINARY_DOUBLE( value )
FROM   long_numbers

Outputs:

| TO_BINARY_DOUBLE(VALUE) |
| :---------------------- |
| 1.1231232112312312E+126 |
| 1.1231232112312312E+125 |
| 1.2345678901234568E-110 |

db<>fiddle here

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142993

Like this, perhaps? Enclose it into single quotes (so that it becomes a string) and apply SUBSTR to it:

SQL> select substr('1123123211231231231231231231231231231123123123123123123123213213123213123213123123213123123123123213123123123126666666355555899', 1, 40) result from dual;

RESULT
----------------------------------------
1123123211231231231231231231231231231123

SQL>

Upvotes: 1

Related Questions