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)

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

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

Arguments

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 date format model. This model specifies how the conversion from a DATETIME data type to NTEXT should be performed. For information about date format models, see the Oracle Database SQL Reference and the Oracle Database Globalization Support Guide. 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 23-23, "Specifying the Default Language and a Date Format". Do not use options that set other options. See "Specifying Options".

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 numerical data type (NUMBER, INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL) to TEXT should be performed. For information about number format models, see the Oracle Database SQL Reference and the Oracle Database Globalization Support Guide.

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. (See NLS Options.)

Notes

NTEXT Always UTF8 Unicode

The return value of the TO_NCHAR function has the NTEXT data type, which is always in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16.

Similarity to SQL TO_NCHAR Function

The OLAP DML TO_NCHAR function has functionality similar to that of the SQL TO_NCHAR function. For more information about the SQL TO_NCHAR function, see Oracle Database SQL Reference.

Support for Numerical Data Types

The TO_NCHAR function converts INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, and SHORTDECIMAL values to NUMBER before converting them to NTEXT. Thus, TO_NCHAR converts NUMBER values faster than other numerical data types.

Output Date Format

A converted date has the format specified by the NLS_DATE_FORMAT option. (See NLS Options.)

Rounding

All number format models cause the number to be rounded to the specified number of significant digits. Table 23-3, "Possible Effects of Rounding" identifies some of the effects of rounding.

Specifying Options

Options that set other options should not be used in this command. For example, do not set NLS_LANGUAGE or NLS_TERRITORY. Set NLS_DATE_LANGUAGE instead.

While TO_NCHAR will save and restore the current setting of the specified option so that it has a new value only for the duration of the command, 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 will be 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 command, and again when the saved value of NLS_TERRITORY is restored.

Simple Data Type Conversion

For simple data type conversion, use CONVERT.

Examples

Example 23-21 Date Conversion

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 23-22 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 23-23 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