TO_NCHAR

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.

Return Value

NTEXT

Syntax

TO_NCHAR(text-exp)

or

TO_NCHAR(datetime-exp, [datetime-fmt,] [option setting]

or

TO_NCHAR(num-exp, [num-fmt,] [nlsparams]

Parameters

text-exp

A TEXT expression to be converted to NTEXT.

datetime-exp

A DATETIME expression to be converted to NTEXT.

datetime-fmt

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.

option setting

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.

num-exp

A numeric expression to be converted to NTEXT.

num-fmt

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.

See also:

"Number Format Models" in Oracle Database SQL Language Reference.

The default number format model uses the decimal and thousands group markers identified by NLS_NUMERIC_CHARACTERS option.

nlsparams

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 

symbol

A text expression that specifies the local currency symbol. It can be no more than 10 characters.

territory

A text expression that identifies the territory whose ISO currency symbol is used.

dg

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.

Examples

SHOW TO_NCHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS')

November  30, 2000 10:01:29

SHOW TO_NCHAR(1013.50, NA, NLS_NUMERIC_CHARACTERS ' .')

1.013 50

NLS_DATE_LANGUAGE = 'spanish'
NLS_DATE_FORMAT = 'Day: Month dd, yyyy HH:MI:SS am'

SHOW TO_NCHAR(SYSDATE)

Viernes  : Diciembre  01, 2000 08:21:17 AM

SHOW TO_NCHAR(SYSDATE, NA, NLS_DATE_LANGUAGE 'german')

Freitag   : Dezember  01, 2000 08:26:00 AM