In Oracle Database, you can format numbers in many ways.
For example, you can format a number as a currency, with commas and decimal points in the right place. You can specify leading zeros, you can add a fractional part – or remove it, if that’s what’s required.
This article contains examples of the following:
- Format numbers as currency
- Add a comma/thousands separator
- Include decimal places
- Remove all decimal places
- Add leading zeros to a number
I also explain how formatting is affected by your session’s NLS parameters.
NLS initialisation parameters determine which characters are used for the group separator, decimal character, and currency symbol in the current session. You can take advantage of these parameters to output locale aware formatting.
You can use functions like TO_CHAR(number) and even LPAD() to convert numbers to a string and format them exactly as you like on the fly. Functions like CAST() can also have an effect on how a number is formatted, depending on the data type that it’s being cast as.
A Quick Example
Here’s a quick example of formatting a number using the TO_CHAR() function:
SELECT
TO_CHAR(12345, 'fmL99G999D00')
FROM DUAL;Result:
$12,345.00
The fmL99G999D00 part is a format model that determines how the output is to be formatted. You can change the format model to suit your requirements.
Each character in the format model is a format element, and has a special meaning. You can add or remove format elements as required.
Here’s a full list of format elements that you can use for your format model when formatting numbers.
While it’s true that you can provide a string literal for various format elements (for example, a dollar sign ($) for dollars), this assumes that the currency is denominated in that hardcoded value. There are many other possible currencies around the world, and the format elements are able to dynamically return the local currency symbol for the user’s session.
It’s the same for the group separator and decimal character. Different locales use different conventions. Using these locale aware format elements makes your code more portable.
Also, the TO_CHAR() function allows you to pass in your own NLS parameters within the function. Doing so only affects that function call, so you can change things like currency symbols on the fly without needing to hardcode it into your format model or update the NLS parameters for your session.
The following examples show how each format element works in more detail.
Format Numbers as Currency
Here’s an example of formatting a number as currency:
SELECT
TO_CHAR(12, 'fmL99')
FROM DUAL;Result:
$12
In this case, I used the L format element to specify the local currency symbol. The local currency symbol is determined by the NLS_CURRENCY parameter.
You can alternatively use the C or U format elements, which return the ISO currency symbol and dual currency symbol respectively.
See How to Format Numbers as Currency in Oracle for more info and examples.
Add a Comma/Thousands Separator
While you can always use a hardcoded comma for your thousands/group separator, this doesn’t take into account countries that use a period for their group separator. The reverse is obviously true. Also, some countries separate thousands groups with a thin space.
You can use the G format element to specify a group separator. This dynamically returns the applicable group separator as specified in the NLS_NUMERIC_CHARACTERS parameter. This parameter determines both the group separator and the decimal character.
Example:
SELECT
TO_CHAR(12345, 'fm99G999')
FROM DUAL;Result:
12,345
Here’s another example with a larger number:
SELECT
TO_CHAR(123456789, 'fm999G999G999')
FROM DUAL;Result:
123,456,789
See How to Format a Number with a Comma in Oracle for a more detailed discussion.
Include Decimal Places
While it’s true that you can hardcode your own radix character (for example, a period) into your format model, this won’t cater for other locales that use a different character.
You can use the D format element to return the decimal/radix character that’s specified in the NLS_NUMERIC_CHARACTERS parameter for the current session:
SELECT
TO_CHAR(7, 'fm9D00')
FROM DUAL;Result:
7.00
In this case I used two 0 format elements after the radix character. This format element returns trailing zeros where applicable.
Using a 9 would suppress any trailing zeros in this case:
SELECT
TO_CHAR(7, 'fm9D99')
FROM DUAL;Result:
7.
However, if we remove the fm format modifier, we get a different result:
SELECT
TO_CHAR(7, '9D99')
FROM DUAL;Result:
7.00
The fm format modifier suppresses any padding that’s been applied to the result. By removing it, our result is padded. It contains a leading space, because this is where a negative sign would have gone if the number had been negative. And it also contains trailing zeros, because we specified two 9 format elements.
See 3 Ways to Format a Number to 2 Decimal Places in Oracle for more ideas for how to format numbers with decimal places.
Remove All Decimal Places
There are several ways to format a number to have no decimal places. One obvious way is to simply remove the decimal part from our format string:
SELECT
TO_CHAR(7, 'fm9')
FROM DUAL;Result:
7
But we can also use other functions, such as ROUND(), TRUNC(), and CAST() to achieve the same, or similar effect.
See 4 Ways to Format a Number Without Decimals in Oracle for examples.
Add Leading Zeros
We have a couple of options when it comes to adding leading zeros to our number.
Again, sticking with the TO_CHAR() function, we can use the 0 format element to return leading and trailing zeros.
SELECT
TO_CHAR(7, 'fm000')
FROM DUAL;Result:
007
If we’d used the 9 format element, we wouldn’t have got any leading zeros:
SELECT
TO_CHAR(7, 'fm999')
FROM DUAL;Result:
7
However, if we remove the fm format modifier, we would have got a space where any leading zero would have been:
SELECT
TO_CHAR(7, '999')
FROM DUAL;Result:
7
Another way to format a number with leading zeros is with the LPAD() function. This function applies left padding to a string or number. You can specify which character/s to use for the padding, and so if you use a zero, then it will be padded with zeros.
See 2 Ways to Format a Number with Leading Zeros in Oracle for an example.
How to Check the NLS Parameters
The NLS (National Language Support) parameters determine the locale-specific behaviour on both the client and the server. This includes parameters that determine which characters to use for the group separator, decimal character, currency symbols, etc.
Let’s check the current value of my NLS parameters:
SELECT
PARAMETER,
VALUE
FROM V$NLS_PARAMETERS;Result:
PARAMETER VALUE __________________________ _________________________________ NLS_LANGUAGE ENGLISH NLS_TERRITORY AUSTRALIA NLS_CURRENCY $ NLS_ISO_CURRENCY AUSTRALIA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD/MON/RR NLS_DATE_LANGUAGE ENGLISH NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH12:MI:SSXFF AM NLS_TIMESTAMP_FORMAT DD/MON/RR HH12:MI:SSXFF AM NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD/MON/RR HH12:MI:SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE
The following parameters determine formatting elements for numbers and currencies:
NLS_CURRENCYNLS_ISO_CURRENCYNLS_NUMERIC_CHARACTERSNLS_DUAL_CURRENCY
The default value of these parameters is determined by the NLS_TERRITORY parameter. When we set the value of the NLS_TERRITORY parameter, this implicitly sets the value for various other parameters (including the four mentioned).
In my case, my territory is Australia, and so these four parameters reflect how numbers are typically formatted in Australia. If I were to change it to say, Germany, then those four parameters would be updated to reflect the formatting for Germany.
However, you can explicitly set each parameter individually. This allows you to override the value that was implicitly set by the NLS_TERRITORY parameter.