The TO_NCHAR function converts a TEXT expression, date, or number to NTEXT in a specified format. This function is typically used to format output data.
TO_NCHAR(text-exp)
or
TO_NCHAR(datetime-exp, [datetime-fmt,] [option setting]
or
TO_NCHAR(num-exp, [num-fmt,] [nlsparams]
A TEXT expression to be converted to NTEXT.
A DATETIME expression to be converted to NTEXT.
A text expression that identifies a datetime format template. This template specifies how the conversion from a DATETIME data type to NTEXT should be performed. For information about datetime format templates, see Table 9-4, "Datetime Format Elements". The default value of datetime-fmt is controlled by the NLS_DATE_FORMAT option.
An OLAP option (such as NLS_DATE_LANGUAGE) and its new setting, which temporarily overrides the setting currently in effect for the session. Typically, this option identifies the language that you want datetime-exp to be translated into. See Example 8-143, "Specifying the Default Language and a Date Format".
Do not specify an option that sets other options. For example, do not set NLS_LANGUAGE or NLS_TERRITORY; set NLS_DATE_LANGUAGE instead.While TO_NCHAR saves and restores the current setting of the specified option so that it has a new value only for the duration of the statement, TO_NCHAR cannot save and restore any side effects of changing that option. For example, NLS_TERRITORY controls the value of NLS_DATE_FORMATE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_CALENDAR, and other options. When you change the value of NLS_TERRITORY in a call to TO_NCHAR, all of these options are reset to their territory-appropriate default values twice: once when NLS_TERRITORY is set to its new value for the duration of the TO_NCHAR statement, and again when the saved value of NLS_TERRITORY is restored.
A numeric expression to be converted to NTEXT.
A text expression that identifies a number format model. This model specifies how the conversion from a numeric data type (NUMBER, INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL) to TEXT should be performed.
The default number format model uses the decimal and thousands group markers identified by NLS_NUMERIC_CHARACTERS option.
A text expression that specifies the thousands group marker, decimal marker, and currency symbols used in num-exp. This expression contains one or more of the following parameters, separated by commas:
NLS_CURRENCY symbol
NLS_ISO_CURRENCY territory
NLS_NUMERIC_CHARACTERS dg
A text expression that specifies the local currency symbol. It can be no more than 10 characters.
A text expression that identifies the territory whose ISO currency symbol is used.
A text expression composed of two different, single-byte characters for the decimal marker (d) and thousands group marker (g).
These parameters override the default values specified by the NLS_CURRENCY, NLS_ISO_CURRENCY, and NLS_NUMERIC_CHARACTERS options.
This statement converts today's date and specifies the format.
SHOW TO_NCHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS')
The specified date format allows the time to be displayed along with the date.
November 30, 2000 10:01:29
Example 8-142 Converting Numerical Data to NTEXT Data
This statement converts a number to NTEXT and specifies a space as the decimal marker and a period as the thousands group marker.
SHOW TO_NCHAR(1013.50, NA, NLS_NUMERIC_CHARACTERS ' .')
The value 1013.50 now appears like this:
1.013 50
Example 8-143 Specifying the Default Language and a Date Format
The following statements set the default language to Spanish and specify a new date format.
NLS_DATE_LANGUAGE = 'spanish' NLS_DATE_FORMAT = 'Day: Month dd, yyyy HH:MI:SS am'
The following statement:
SHOW TO_NCHAR(SYSDATE)
Displays the current date and time in Spanish:
Viernes : Diciembre 01, 2000 08:21:17 AM
The NLS_DATE_LANGUAGE option changes the language for the duration of the statement. The following statement
SHOW TO_NCHAR(SYSDATE, NA, NLS_DATE_LANGUAGE 'german')
displays the date and time in German:
Freitag : Dezember 01, 2000 08:26:00 AM