CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
Sree Bhanu
Sree Bhanu

Reputation: 89

format a lengthy number with commas in Oracle

I have a requirement to convert the very lengthy amount to a comma separated value in oracle. I was searching in google. but I got some solutions which works only for small numbers. But not for lengthy number. Below is the solution I have. But not working properly. I was getting ############... if I run the below. 

SELECT TO_CHAR(6965854565787645667634565432234565432345643265432345643242087,
'99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."') as test
FROM dual;

Desired output:

6,965,854,565,787,645,667,634,565,432,234,565,432,345,643,265,432,345,643,242,087

Please help me. thanks in advance.

Upvotes: 1

Views: 6050

Answers (4)

user5683823
user5683823

Reputation:

@AlexPoole pointed out that perhaps your input is a string.

I didn't get that vibe; but if in fact your input IS a string, and if you know the length is no more than 99 digits, you could do something like below. If your strings can be longer than 99, replace 99 below with a sufficiently large multiple of 3. (Or, you can replace it with a calculated value, 3 * ceil(length(str)/3)).

with
     inputs ( str ) as (
       select '12345678912345' from dual
     )
-- WITH clause is only for testing/illustration, not part of the solution
select ltrim(regexp_replace(lpad(str, 99, ','), '(.{3})', ',\1'), ',') as test
from   inputs;

TEST
------------------
12,345,678,912,345

Upvotes: 1

BobC
BobC

Reputation: 4416

    SELECT TO_CHAR(
    6676345654322345654323456432654323456, 
    '999G999G999G999G999G999G999G999G999G999G999G999G999',
'NLS_NUMERIC_CHARACTERS=",."') as test FROM dual

    TEST
    ------------------------------------------------------------
       6,676,345,654,322,345,654,323,456,432,654,323,456

Upvotes: 1

Tajinder
Tajinder

Reputation: 2338

Please check if below query can help.

SELECT ltrim(regexp_replace('00'
  || '6965854565787645667634565432234565432345643265432345643242087', '(...)', ',\1' ),',|0') AS t
FROM dual;

Upvotes: 2

user5683823
user5683823

Reputation:

Numbers in Oracle can't have more than 38 significant digits. You have many more than that.

If I may, what kind of "amount" is that? My understanding is that Oracle was designed to handle real-life values. What possible meaning is there to the sample number you posted?

Added: Original poster in a comment (below) stated that he is getting the same error with a shorter number, only 34 digits.

Two issues. First, the format model must have at least the needed number of digits (of 9's). to_char(100000, '9G999') will produce the output #### because the format model allows only 4 digits, but the input is 6 digits.

Then, after that is corrected, the output may still look incorrect in the front-end application, like SQL*Plus. In SQL*Plus the default width of a number column is 10 (I believe). That can be changed to 38, for example with the command set numwidth 38. In other front-ends, like Toad and SQL Developer, the default numeric width is a setting that can be changed through the graphical user interface.

More added - actually the result of to_char is a string, and by default strings of any length should be displayed OK in any front-end, so the numeric width is probably irrelevant. (And, in any case, it does not affect the displaying of strings, including the result of to_char().)

Upvotes: 1

Related Questions