Purpose
This function is used to convert numeric data into VARCHAR type data.
Syntax
TO_CHAR(number [, fmt])
Parameters
number: The numeric data to be converted.fmt: Optional. Specifies the format for the output value. If omitted,numberis converted to aVARCHARvalue. For more information, see fmt.
fmt
The fmt parameter can be set to the following values:
- Numeric:
0and9. - Grouping classes:
.,,,D, andG. - [Currency types](#Currency types):
$,C,L, andU. - [Arithmetic conversion categories](#Arithmetic conversion categories):
EEEE,RN,V, andX. - Signs:
MI,PR, andS. - Orthodox:
TM. - [Other classes](#Other classes):
BandFM.
Numeric
0: A placeholder indicating a numeric position. Returns the corresponding character at that position; if no character exists, fills with0.Notice
0is a mandatory symbol. If the corresponding bit is absent, it is filled with0. This is a feature that differs from9.Example:
SELECT TO_CHAR(123, '00000'), TO_CHAR(123.456, '0000.0000');The return result is as follows:
+-----------------------+-------------------------------+ | TO_CHAR(123, '00000') | TO_CHAR(123.456, '0000.0000') | +-----------------------+-------------------------------+ | 00123 | 0123.4560 | +-----------------------+-------------------------------+ 1 row in set9: A placeholder indicating a digit position. Specifically:For decimal places, it indicates converting to the corresponding character. If there is no such character,
0is used for padding.For integer digits, no character is padded if there is no corresponding value.
Example:
SELECT TO_CHAR(123, '999999'), TO_CHAR(123.456, '9999.9999');The return result is as follows:
+------------------------+-------------------------------+ | TO_CHAR(123, '999999') | TO_CHAR(123.456, '9999.9999') | +------------------------+-------------------------------+ | 123 | 123.4560 | +------------------------+-------------------------------+ 1 row in set
Grouping
.(period): Indicates a decimal point, located at the specified position. If the fractional part is insufficient, zeros are padded; if the fractional part exceeds the number of digits specified infmt, rounding is performed.Notice
fmtcan specify only one decimal point.Example:
SELECT TO_CHAR(123.555, '999.99'), TO_CHAR(123.5, '999.99');The return result is as follows:
+----------------------------+--------------------------+ | TO_CHAR(123.555, '999.99') | TO_CHAR(123.5, '999.99') | +----------------------------+--------------------------+ | 123.56 | 123.50 | +----------------------------+--------------------------+ 1 row in set,(English comma): Typically appears in the thousandth place as a grouping symbol. If necessary, it can also represent the tenth or hundredth place and may appear up to N times, depending on the size of the number.Notice
,cannot be at the beginning offmtand can only appear in the integer part.Example:
SELECT TO_CHAR(123456, '99,99,99'), TO_CHAR(123456789, '999,999,999');The return result is as follows:
+-----------------------------+-----------------------------------+ | TO_CHAR(123456, '99,99,99') | TO_CHAR(123456789, '999,999,999') | +-----------------------------+-----------------------------------+ | 12,34,56 | 123,456,789 | +-----------------------------+-----------------------------------+ 1 row in setD(ISO decimal character): The international version (ISO) of the "decimal point" character. It has the same function as.and can appear only once. The difference is that this character uses the default value of theNLS_NUMERIC_CHARACTERparameter.Notice
Generally, do not use this format symbol unless necessary, and do not modify the parameter value (
NLS_NUMERIC_CHARACTER) lightly.Example:
SELECT TO_CHAR(123.555, '999D99'), TO_CHAR(123.5, '999D99');The return result is as follows:
+----------------------------+--------------------------+ | TO_CHAR(123.555, '999D99') | TO_CHAR(123.5, '999D99') | +----------------------------+--------------------------+ | 123.56 | 123.50 | +----------------------------+--------------------------+ 1 row in setG: The ISO standard for a "comma", used as a grouping symbol and can be applied in multiple locations.Notice
If you want to convert a number into a decimal point, you must use it with
D, not with the period (.).Example:
SELECT TO_CHAR(123456, '99G99G99'), TO_CHAR(123456789, '999G999G999');The return result is as follows:
+-----------------------------+-----------------------------------+ | TO_CHAR(123456, '99G99G99') | TO_CHAR(123456789, '999G999G999') | +-----------------------------+-----------------------------------+ | 12,34,56 | 123,456,789 | +-----------------------------+-----------------------------------+ 1 row in set
Currency
$(dollar sign): Returns the value with a leading dollar sign.Notice
$can be placed anywhere in thefmtstring, but it must appear only once.Example:
SELECT TO_CHAR(123.45, '$999.99'), TO_CHAR(123.45, '9,9,9,9.$99');The return result is as follows:
+----------------------------+--------------------------------+ | TO_CHAR(123.45, '$999.99') | TO_CHAR(123.45, '9,9,9,9.$99') | +----------------------------+--------------------------------+ | $123.45 | $1,2,3.45 | +----------------------------+--------------------------------+ 1 row in setC(International Currency Symbol): Returns the ISO currency symbol (the value represented by the currentNLS_ISO_CURRENCYparameter) at the specified position.Notice
Ccan only appear as the first character in the integer part offmt.Example:
SELECT TO_CHAR(123.45, 'C999.99');The return result is as follows:
+----------------------------+ | TO_CHAR(123.45, 'C999.99') | +----------------------------+ | USD123.45 | +----------------------------+ 1 row in setL(local currency symbol): the local version ofC, which returns the local currency symbol at the specified position. It can be placed at the very beginning or end of the entire format.Example:
SELECT TO_CHAR(123.45, 'L999.99'), TO_CHAR(123.45, '999.99L');The return result is as follows:
+----------------------------+----------------------------+ | TO_CHAR(123.45, 'L999.99') | TO_CHAR(123.45, '999.99L') | +----------------------------+----------------------------+ | $123.45 | 123.45$ | +----------------------------+----------------------------+ 1 row in setU: Returns the euro (or other) currency symbol determined by the current value represented by theNLS_DUAL_CURRENCYparameter at the specified position.Example:
SELECT TO_CHAR(123.45, 'U999.99'), TO_CHAR(123.45, '999.99U'), TO_CHAR(123.45, '999U999');The return result is as follows:
+----------------------------+----------------------------+----------------------------+ | TO_CHAR(123.45, 'U999.99') | TO_CHAR(123.45, '999.99U') | TO_CHAR(123.45, '999U999') | +----------------------------+----------------------------+----------------------------+ | $123.45 | 123.45$ | 123$450 | +----------------------------+----------------------------+----------------------------+ 1 row in set
Compute conversion classes
EEEE(scientific notation): Returns the value in scientific notation. Since this is a scientific calculation method, adding one or more leading 9s or 0s is sufficient; more than one leading digit is meaningless.Example:
SELECT TO_CHAR(10000, '9EEEE'), TO_CHAR(10000, '9.9EEEE'), TO_CHAR(10000, '999.9EEEE');The return result is as follows:
+-------------------------+---------------------------+-----------------------------+ | TO_CHAR(10000, '9EEEE') | TO_CHAR(10000, '9.9EEEE') | TO_CHAR(10000, '999.9EEEE') | +-------------------------+---------------------------+-----------------------------+ | 1E+04 | 1.0E+04 | 1.0E+04 | +-------------------------+---------------------------+-----------------------------+ 1 row in setRN(rn): Converts an integer (between 1 and 3,999) to Roman numerals.RNindicates uppercase conversion, whilernindicates lowercase conversion.Notice
RN(rn) cannot be combined with other symbols.Example:
SELECT TO_CHAR(99, 'RN'), TO_CHAR(99, 'rn');The return result is as follows:
+-------------------+-------------------+ | TO_CHAR(99, 'RN') | TO_CHAR(99, 'rn') | +-------------------+-------------------+ | XCIX | xcix | +-------------------+-------------------+ 1 row in setV: Returns a value multiplied by10^n(rounded if necessary), wherenis the number of 9s followingV.Notice
Vcannot be used together with decimal expressions in thefmtformat, but can be mixed with symbols such as currency.Example:
SELECT TO_CHAR(123.456, '999V99'), TO_CHAR(123.4, '999V999'), TO_CHAR(5, '9V');The return result is as follows:
+----------------------------+---------------------------+------------------+ | TO_CHAR(123.456, '999V99') | TO_CHAR(123.4, '999V999') | TO_CHAR(5, '9V') | +----------------------------+---------------------------+------------------+ | 12346 | 123400 | 5 | +----------------------------+---------------------------+------------------+ 1 row in setX: Returns a hexadecimal value of the specified length. If the specified number is not an integer, it is rounded to an integer. The following restrictions apply:This element accepts only positive values or 0. Negative values return an error.
You can only add 0 (leading zeros) or FM in front of this element. Any other element will return an error. If you use X without specifying 0 or FM, the return value always has a leading space.
Example:
SELECT TO_CHAR(10, 'X'), TO_CHAR(10, 'XXXX');The result is as follows:
+------------------+---------------------+ | TO_CHAR(10, 'X') | TO_CHAR(10, 'XXXX') | +------------------+---------------------+ | A | A | +------------------+---------------------+ 1 row in set
Positive and negative signs
MI(mi): Returns a negative value with a trailing minus sign (-). If the number is positive, a space is appended to the end.Notice
MI-formatted elements can only appear at the end of a numeric format model.Example:
SELECT TO_CHAR(-123, '999MI'), TO_CHAR(-123, '999mi');The return result is as follows:
+------------------------+------------------------+ | TO_CHAR(-123, '999MI') | TO_CHAR(-123, '999mi') | +------------------------+------------------------+ | 123- | 123- | +------------------------+------------------------+ 1 row in setPR: Another way to represent negative numbers:If it is a positive number, add a space at the beginning.
If it is a negative number, enclose the number in angle brackets
<>.Notice
PR-formatted elements can only appear at the very end of a numeric format model.Example:
SELECT TO_CHAR(-1234.89,'9G999D00PR'), TO_CHAR(1234.89,'9G999D00PR');The return result is as follows:
+--------------------------------+-------------------------------+ | TO_CHAR(-1234.89,'9G999D00PR') | TO_CHAR(1234.89,'9G999D00PR') | +--------------------------------+-------------------------------+ | <1,234.89> | 1,234.89 | +--------------------------------+-------------------------------+ 1 row in set
S: A positive number is prefixed with a plus sign (+), and a negative number is prefixed with a minus sign (-).Notice
S-formatted elements can only appear at the beginning or end of a numeric format model.Example:
SELECT TO_CHAR(-123.45,'S999.99'), TO_CHAR(123.45,'S999.99');The return result is as follows:
+----------------------------+---------------------------+ | TO_CHAR(-123.45,'S999.99') | TO_CHAR(123.45,'S999.99') | +----------------------------+---------------------------+ | -123.45 | +123.45 | +----------------------------+---------------------------+ 1 row in set
Orthodox
TM: The default value is TM9. Unless the output exceeds 64 characters, it returns the number with fixed symbols. If the output exceeds 64 characters, the number is automatically returned in scientific notation. When a number exceeds 64 bits, the output of TM9 is equivalent to that of TME.
Example:
SELECT TO_CHAR(1234, 'TM9'), TO_CHAR(1234, 'TME');
The returned result is as follows:
+----------------------+----------------------+
| TO_CHAR(1234, 'TM9') | TO_CHAR(1234, 'TME') |
+----------------------+----------------------+
| 1234 | 1.234E+03 |
+----------------------+----------------------+
1 row in set
Other classes
B(space): A space character can be added before the integer part. It can appear anywhere.Notice
Bcan only appear in the integer part.Example:
SELECT TO_CHAR(1234, '99B99'), TO_CHAR(1234, '9999B'), TO_CHAR(1234, 'B9999');The return result is as follows:
+------------------------+------------------------+------------------------+ | TO_CHAR(1234, '99B99') | TO_CHAR(1234, '9999B') | TO_CHAR(1234, 'B9999') | +------------------------+------------------------+------------------------+ | 1234 | 1234 | 1234 | +------------------------+------------------------+------------------------+ 1 row in setFM(fm): Remove leading spaces.Notice
FMcan only appear as the first digit of the integer part offmt.Example:
SELECT TO_CHAR(' 123.456', 'fm999.999'), TO_CHAR(' 123.456', 'FM999.999');The return result is as follows:
+-----------------------------------+-----------------------------------+ | TO_CHAR(' 123.456', 'fm999.999') | TO_CHAR(' 123.456', 'FM999.999') | +-----------------------------------+-----------------------------------+ | 123.456 | 123.456 | +-----------------------------------+-----------------------------------+ 1 row in set
Return type
- Returns data of the
VARCHARtype. - Returns
NULLwhen thenumbervalue isNULL.
Examples
SELECT
TO_CHAR(11111.111, '999,999.9999'),
TO_CHAR(11111.111, '000,000.0000'),
TO_CHAR(55555.555, '99,999.99'),
TO_CHAR(55555.555, '99,999V99'),
TO_CHAR(NULL, '999.999');
The return result is as follows:
+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
| TO_CHAR(11111.111, '999,999.9999') | TO_CHAR(11111.111, '000,000.0000') | TO_CHAR(55555.555, '99,999.99') | TO_CHAR(55555.555, '99,999V99') | TO_CHAR(NULL, '999.999') |
+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
| 11,111.1110 | 011,111.1110 | 55,555.56 | 55,55556 | NULL |
+------------------------------------+------------------------------------+---------------------------------+---------------------------------+--------------------------+
1 row in set