hakobot hakobot - 4 months ago 9
SQL Question

How add separating blank space in numeric value

Unfortunately I couldn't find solutions at doc's.

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;

Answer

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     
Comments