如何在数值中添加分隔空格(How add separating blank space in numeric value)

如何在数值中添加分隔空格(How add separating blank space in numeric value)

不幸的是我在文档中找不到解决方案。

我想获得特定格式的数字，如下所示：

234652.24 --> 234 652.24 42145124 --> 42 145 124 select employee_id, to_char(salary, '??????') as "Salary from employees;

Unfortunately I couldn't find solutions in the docs.

I want to get numbers in specific format, like this:

234652.24 --> 234 652.24 42145124 --> 42 145 124 select employee_id, to_char(salary, '??????') as "Salary from employees;

最满意答案

您可以将NLS_NUMERIC_CHARTACTERS设置指定为TO_CHAR()调用的一部分 ，并使用G和D 格式模型占位符：

with employees (employee_id, salary) as ( select 1, 234652.24 from dual --> 234 652.24 union all select 2, 42145124 from dual --> 42 145 124 ) select employee_id, to_char(salary, '999G999G999D99', 'NLS_NUMERIC_CHARACTERS=''. ''') as "Salary" from employees; EMPLOYEE_ID Salary ----------- --------------- 1 234 652.24 2 42 145 124.00

如果您不希望第二个值中的尾随零，则可以添加FM格式修饰符，这也会删除前导空格（如果有任何负值，则允许使用减号）; 但这仍然是落后期; 你可以使用RTRIM()摆脱它：

with employees (employee_id, salary) as ( select 1, 234652.24 from dual --> 234 652.24 union all select 2, 42145124 from dual --> 42 145 124 ) select employee_id, rtrim(to_char(salary, 'FM999G999G999D99', 'NLS_NUMERIC_CHARACTERS=''. '''), '.') as "Salary" from employees; EMPLOYEE_ID Salary ----------- --------------- 1 234 652.24 2 42 145 124

You can specify the NLS_NUMERIC_CHARTACTERS setting as part of the TO_CHAR() call, and use the G and D format model placeholders:

with employees (employee_id, salary) as ( select 1, 234652.24 from dual --> 234 652.24 union all select 2, 42145124 from dual --> 42 145 124 ) select employee_id, to_char(salary, '999G999G999D99', 'NLS_NUMERIC_CHARACTERS=''. ''') as "Salary" from employees; EMPLOYEE_ID Salary ----------- --------------- 1 234 652.24 2 42 145 124.00

If you don't want the trailing zeros in the second value you can add the FM format modifier, which also removes the leading space (which is there to allow for a minus sign if there are any negative values); but that still leaves the trailing period; you can use RTRIM() to get rid of that:

with employees (employee_id, salary) as ( select 1, 234652.24 from dual --> 234 652.24 union all select 2, 42145124 from dual --> 42 145 124 ) select employee_id, rtrim(to_char(salary, 'FM999G999G999D99', 'NLS_NUMERIC_CHARACTERS=''. '''), '.') as "Salary" from employees; EMPLOYEE_ID Salary ----------- --------------- 1 234 652.24 2 42 145 124