Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, swadesh.

Asked: December 08, 2016 - 2:17 pm UTC

Answered by: Chris Saxon - Last updated: December 08, 2016 - 3:00 pm UTC

Category: Database - Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked


Hi,


Please inform how can replace decimal operator dot(.) with comma(,) in oracle database

Permanently I want to change it in database.

Also no separator is required for numaric values.All decimal fields of table will accept comma(,) instead of dot(.)

Actually my customer is Sweden based .This is business requirement.

The result of the following statement wil be just like below....

OLD(default)- 1,234.56
NOW- 1234,56


Regards,
Swadesh Mondal

and we said...

What you display for the group and decimal separators depends upon your NLS settings. To switch dots and commas, change the NLS_NUMERIC_CHARACTERS:

create table t (
  x number
);

insert into t values (1234.56);

alter session set NLS_NUMERIC_CHARACTERS = '.,';

select * from t;

alter session set NLS_NUMERIC_CHARACTERS = ',.';

select * from t;


Note this setting is in the client. So the number is still stored in the same way. Different people in different locations can view it according to their preferences.

For further control over how you display the number, use to_char with the appropriate format mask:

select to_char(x, '999G999D00') sep, to_char(x, '999990D00') no_sep
from   t;

SEP         NO_SEP   
----------- ----------
   1.234,56    1234,56


http://docs.oracle.com/database/122/SQLRF/Format-Models.htm#SQLRF00211

Is this answer out of date? If it is, please let us know via a Review