TO_CHAR(Numeric) Function | Data Types and Literals | Teradata Vantage - TO_CHAR(Numeric) - Teradata Vantage - Analytics Database

Converts numeric_expr to a character string.

TO_CHAR(Numeric) is a scalar function whose return data type is VARCHAR CHARACTER SET UNICODE.

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

Syntax

[TD_SYSFNLIB.] TO_CHAR (
  numeric_expr [, format_arg [, NLS_param ] ]
)

Syntax Elements

TD_SYSFNLIB.

Name of the database where the function is located.

numeric_expr

A numeric argument.

If the conversion fails, '#' characters are returned. When the integer portion of the number is less than the digits specified in the format string, a string with a variable number of '#' characters is returned. The number of '#' characters returned is equal to the length of the maximum possible character string result based on the format string or data type of numeric_expr. This includes a '#' character representing the optional plus/minus sign.

For example:

SELECT TO_CHAR(12345678,'99')

returns '###'.

SELECT TO_CHAR(12345678,'99999')

returns '######'.

SELECT TO_CHAR(12345678,'99V9')

returns '####'.

format_arg

A character expression.

format_arg is used to format the numeric values. If format_arg is omitted, numeric_expr is converted to a string exactly long enough to hold its significant digits.

If this syntax element is not valid, an error is returned.

For format_arg values, see TO_NUMBER.

NLS_param

A character expression.

NLS_param specifies characters that are returned by number format elements:

• Decimal character
• Group separator
• Local currency symbol
• Dual currency symbol
• International currency symbol

Valid values for param are:

• NUMERIC_CHARACTERS = ' 'dg' '
• CURRENCY = ' ' text ' '
• DUAL_CURRENCY = ' ' text ' '
• ISO_CURRENCY= ' ' text ' '

The characters d and g represent the decimal character and group separator respectively. They must be different single-byte characters. Text must be enclosed in apostrophes. Ten characters are available for the currency symbol.

The SDF (Specification for Data Formatting) file is used to determine any default formatting. If NLS_param is specified, it overrides any defaults specified in the SDF file.

If NLS_param is NULL, NULL is returned.

For NLS_param values, see TO_NUMBER.