About the NLS_NUMERIC_CHARACTERS Parameter

Specifies: Decimal character (which separates the integer and decimal parts of a number) and group separator (which separates integer groups to show thousands and millions, for example). The group separator is the character returned by the numeric format element G.

Acceptable Values: Any two different single-byte characters except:

A numeric character
Plus (+)
Minus (-)
Less than (<)
Greater than (>)

Default Value: Set by NLS_TERRITORY, described in "About the NLS_TERRITORY Parameter".

In a SQL statement, you can represent a number as either:

Numeric literal

A numeric literal is not enclosed in quotation marks, always uses a period (.) as the decimal character, and never contains a group separator.
Text literal

A text literal is enclosed in single quotation marks. It is implicitly or explicitly converted to a number, if required, according to the current NLS settings.

The following example shows how two different NLS_NUMERIC_CHARACTERS settings affect the displayed result of the same query.

To try this example in SQL Developer, enter the statements and queries in the SQL Worksheet. For information about the SQL Worksheet, see "Running Queries in SQL Developer". The results shown here are from SQL*Plus; their format is slightly different in SQL Developer.

NLS_NUMERIC_CHARACTERS Affects Decimal Character and Group Separator

Note the current value of NLS_NUMERIC_CHARACTERS.

For instructions, see "Viewing NLS Parameter Values".
If the value of NLS_NUMERIC_CHARACTERS in step 1 is not ",." (decimal character is comma and group separator is period), change it:
```
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=",.";
```

Run this query:

SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;

Result:

Number
---------
 4.000,00

Change the value of NLS_NUMERIC_CHARACTERS to ",." (decimal character is period and group separator is comma):
```
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
```

Run this query:

SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;

Result:

Number
---------
 4,000.00

Set NLS_NUMERIC_CHARACTERS to the value that it had at step 1.

About the NLS_NUMERIC_CHARACTERS Parameter

Related Topics