Oracle APEX number format mask without comma-CodePudding

I am trying to change the number eg. 1000 to 1.000, 10000 to 10.000 istead of 10,000 or 10,000.00

DO you have any idea? :)

CodePudding user response：

As far as I can tell, there's (unfortunately) no declarative way to modify thousands/decimal separators. Should be somewhere in "Edit application definition - Globalization" settings, but - there's nothing like that there.

So, do it yourself, manually. Navigate to shared components - security attributes - database session tab - initialization PL/SQL code and put this into it:

begin
  execute immediate q'[alter session set nls_numeric_characters = ', ']';
end;

which will set comma as decimal separator, and space as thousands (group) separator.

Example:

SQL> alter session set nls_numeric_characters = ', ';

Session altered.

SQL> select 5000 val1,
  2         to_char(5000, '999G990D00') val2,
  3         to_char(5000, '999G990') val3
  4  from dual;

      VAL1 VAL2        VAL3
---------- ----------- --------
      5000    5 000,00    5 000

SQL>

CodePudding user response：

You can use

SELECT TO_CHAR(your_nr_col, '999G999G999G990', 'nls_numeric_characters='',.''') AS nr_formatted
  FROM t

if you're dealing with integers only ( without including any decimal separator ), put as much as 9s to exceed the number of the digits while adding Gs after each three padded digit starting from the right hand side.

If there might arise some cases in which the decimal separators are needed, then replace the second argument with '999G999G999G990D0000' to pad enough 0s after single D character directing from left to right

DemO