|
| |
| 15. 7. 1. Format Parameters |
|
|
TO_CHAR(number, format, NLS_Params) |
The format mask and the NLS parameters are identical to the TO_NUMBER function. |
The NLS parameters again are |
- NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
- NLS_CURRENCY -- Specifies the local currency.
- NLS_ISO_CURRENCY -- Character(s) to represent the ISO currency symbol.
|
The optional format string you may pass to TO_CHAR() has a number of parameters that affect the string returned by TO_CHAR(). |
Some of these parameters are listed in the following table. |
| Parameter | Format Examples | Description | | 9 | 999 | Returns digits in specified positions with leading negative sign if the number is negative. | | 0 | 0999 9990 | 0999: Returns a number with leading zeros.9990: Returns a number with trailing zeros. | | . | 999.99 | Returns a decimal point in the specified position. | | , | 9,999 | Returns a comma in the specified position. | | $ | $999 | Returns a leading dollar sign. | | B | B9.99 | If the integer part of a fixed point number is zero, returns spaces for the zeros. | | C | C999 | Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter. | | D | 9D99 | Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character). | | EEEE | 9.99EEEE | Returns number using the scientific notation. | | FM | FM90.9 | Removes leading and trailing spaces from number. | | G | 9G999 | Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter. | | L | L999 | Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter. | | MI | 999MI | Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space. | | PR | 999PR | Returns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces. | | RN rn | RN rn | Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999. | | S | S999 999S | S999: Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign.999S: Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign. | | TM | TM | Returns a number using the minimum number of characters. Default is TM9, which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation. | | U | U999 | Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter. | | V | 99V99 | Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded. | | X | XXXX | Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer. |
|
Quote from: |
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback) |
# Paperback: 608 pages |
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004) |
# Language: English |
# ISBN-10: 0072229810 |
# ISBN-13: 978-0072229813 |
| 15. 7. TO_CHAR | | 15. 7. 1. | Format Parameters | | | | 15. 7. 2. | TO_CHAR(x [, format]) converts x to a string. | | | | 15. 7. 3. | TO_CHAR() will return a string of pound characters (#) if you try and format a number that contains too many digits for the format you have provided. | | | | 15. 7. 4. | Use TO_CHAR() to convert columns containing numbers to strings. | | | | 15. 7. 5. | TO_CHAR(12345.67, '99,999.99') (1) | | | | 15. 7. 6. | TO_CHAR(12345.67, '99999.99') (2) | | | | 15. 7. 7. | TO_CHAR(-12345.67, '99,999.99') (3) | | | | 15. 7. 8. | TO_CHAR(12345.67, '099,999.99') (4) | | | | 15. 7. 9. | TO_CHAR(12345.67, '99,999.9900') | | | | 15. 7. 10. | TO_CHAR(12345.67, '$99,999.99') | | | | 15. 7. 11. | TO_CHAR(0.67, 'B9.99') | | | | 15. 7. 12. | TO_CHAR(12345.67, 'C99,999.99') | | | | 15. 7. 13. | TO_CHAR(12345.67, '99999D99') | | | | 15. 7. 14. | TO_CHAR(12345.67, '99999.99EEEE') | | | | 15. 7. 15. | TO_CHAR(0012345.6700, 'FM99999.99') | | | | 15. 7. 16. | TO_CHAR(12345.67, '99999G99') | | | | 15. 7. 17. | TO_CHAR(12345.67, 'L99,999.99') | | | | 15. 7. 18. | TO_CHAR(-12345.67, '99,999.99MI') | | | | 15. 7. 19. | TO_CHAR(-12345.67, '99,999.99PR') | | | | 15. 7. 20. | TO_CHAR(2007, 'RN') | | | | 15. 7. 21. | TO_CHAR(12345.67, 'TM') | | | | 15. 7. 22. | TO_CHAR(12345.67, 'U99,999.99') | | | | 15. 7. 23. | TO_CHAR(12345.67, '99999V99') | | | | 15. 7. 24. | TO_CHAR(last_ddl_time,'dd-mon-yyyy hh24:mi') | | | | 15. 7. 25. | to_char(sysdate,'hh24:mi:ss') as time | | | | 15. 7. 26. | to_char(to_date('01/01/2006','dd/mm/yyyy'),is on Day') as new_year_2006 | | | | 15. 7. 27. | to_char(sysdate, 'Day', 'nls_date_language=Dutch') | | | | 15. 7. 28. | to_char(sysdate,'DAY dy Dy') | | | | 15. 7. 29. | to_char(sysdate,'MONTH mon') as month | | | | 15. 7. 30. | to_char(bdate,'fmMonth ddth, yyyy') | | | | 15. 7. 31. | use to_char more than once to create long date format | | |
|