sql - Number masking & nls_parameter

Question

So i have a query:

SELECT ROUND(SUM(COLUMN1*(COLUMN3-COLUMN2)),2) FROM .... WHERE ....

EDIT: ROUND to 2 decimals.

It returns this format - example:

  5.25
231.12
   .92

(expected output: # ##0,00):

  5,25
231,12
  0,92

So, how can i mask this correctly ?

SELECT ROUND(
to_number(SUM(COLUMN1*(COLUMN3-COLUMN2)),'[FORMAT_MASK]', '[NLS_PARAM=]')
,0)

标签： sqloraclenumber-formatting

Answer 1

在格式模型中适当地使用TO_CHAR指定十进制字符D

当您的会话默认NLS_NUMERIC_CHARACTERS为,. (" decimal_character group_separator " ) 或您已使用手动设置时

alter session set NLS_NUMERIC_CHARACTERS = ',.'; 

select to_char(231.12, '99999D99')  from dual;

显示

231,12

NLS_NUMERIC_CHARACTERS您可以通过指定为第三个参数来更改行为

select to_char(231.12, '99999D99','NLS_NUMERIC_CHARACTERS='',.''')  from dual;

231,12

演示