Format Parameters : TO_CHAR « Conversion Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Conversion Functions
  3. TO_CHAR

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

  1. NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
  2. NLS_CURRENCY -- Specifies the local currency.
  3. 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.

ParameterFormat ExamplesDescription
9999Returns digits in specified positions with leading negative sign if the number is negative.
00999 99900999: Returns a number with leading zeros.9990: Returns a number with trailing zeros.
.999.99Returns a decimal point in the specified position.
,9,999Returns a comma in the specified position.
$$999Returns a leading dollar sign.
BB9.99If the integer part of a fixed point number is zero, returns spaces for the zeros.
CC999Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter.
D9D99Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character).
EEEE9.99EEEEReturns number using the scientific notation.
FMFM90.9Removes leading and trailing spaces from number.
G9G999Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter.
LL999Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter.
MI999MIReturns a negative number with a trailing minus sign. Returns a positive number with a trailing space.
PR999PRReturns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces.
RN rnRN rnReturns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999.
SS999 999SS999: 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.
TMTMReturns 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.
UU999Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter.
V99V99Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded.
XXXXXReturns 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