TO_CHAR(Numeric) - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1145-015K

TO_CHAR(Numeric)

Purpose  

Converts numeric_expr to a character string.

Syntax  

where:

 

Syntax element …

Specifies …

TD_SYSFNLIB

the 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

an optional character argument.

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 format_arg is not valid, an error is returned.

For format_arg values, see “TO_NUMBER” on page 792.

NLS_param

an optional character argument to be used with format_arg.

For NLS_param values, see “TO_NUMBER” on page 792.

ANSI Compliance

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

Invocation

TO_CHAR(Numeric) is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

Argument Types and Rules

Expressions passed to this function must have the following data types:

  • numeric_expr = BYTEINT, SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, FLOAT/REAL/DOUBLE PRECISION, or NUMBER
  • format_arg = CHAR or VARCHAR
  • NLS_param = CHAR or VARCHAR
  • Result Type

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

    Example  

    The following query:

    SELECT TO_CHAR(50000, '$99,999.99'); 

    returns '$50,000.00'.