TO_CHAR(Numeric) Function | Data Types and Literals | Teradata Vantage - TO_CHAR(Numeric) - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

Converts numeric_expr to a character string.

Syntax

[TD_SYSFNLIB.] TO_CHAR (
  numeric_expr [, format_arg [, NLS_param ] ]
)
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.