Template Patterns for Numeric Formatting
| Pattern | Description |
|---|---|
9
|
Value with the specified number of digits |
0
|
Value with leading zeros |
.
|
Decimal point |
,
|
Group (thousand) separator |
PR
|
Negative value in angle brackets |
S
|
Sign anchored to number (uses locale) |
L
|
Currency symbol (uses locale) |
D
|
Decimal point (uses locale) |
G
|
Group separator (uses locale) |
MI
|
Minus sign in specified position (if number < 0) |
PL
|
Plus sign in specified position (if number > 0) |
SG
|
Plus/minus sign in specified position |
RN
|
Roman numeral (input between 1 and 3999) |
TH/th
|
Ordinal number suffix |
V
|
Shift specified number of digits |
Usage
-
A sign formatted using SG, PL, or MI is not anchored to the number. For example:
=> SELECT to_char(-12, 'S9999'), to_char(-12, 'MI9999'); to_char | to_char ---------+--------- -12 | - 12 (1 row)
- TO_CHAR(-12, 'S9999') produces ' -12'
- TO_CHAR(-12, 'MI9999') produces '- 12'
- 9 results in a value with the same number of digits as there are 9s. If a digit is not available it outputs a space.
- TH does not convert values less than zero and does not convert fractional numbers.
- V effectively multiplies the input values by 10^n, where n is the number of digits following V. TO_CHAR does not support the use of V combined with a decimal point—for example:
99.9V99.