15.00 - TO_NUMBER - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product

Teradata Database

Release Number

15.00

Content Type

Programming Reference

Publication ID

B035-1145-015K

Language

English (United States)

Last Update

2018-09-24

TO_NUMBER

Purpose

Converts string_expr to a NUMBER data type.

Syntax

where:

Syntax element …	Specifies …
TD_SYSFNLIB	the name of the database where the function is located.
string_expr	a character argument. string_expr contains a number in the format specified by the optional format_arg. If conversion fails, NULL is returned.
format_arg	an optional character argument. If format_arg is not valid, the function returns an error. If format_arg is NULL, TO_NUMBER returns a NULL.
NLS_param	an optional character argument. 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.

format_arg Format Elements

Format Element…	Example...	Returns …
, (comma)	9,999	a comma in the specified position. A comma cannot begin a number format. A comma cannot appear to the right of a decimal character or period in a number format.
. (period)	9.99	a decimal point. You can only specify one period in a number format.
$	$9999	a value with a leading dollar sign.
0	09999 9990	leading zeros. trailing zeros.
9	9999	a value with the specified number of digits with a leading space if positive or with a leading minus if negative.
B	B9999	blanks for the integer part of a fixed point number when the integer part is zero.
C	C999	the ISO currency symbol as specified in the ISOCurrency element in the SDF file.
D	99D99	the character that separates the integer and fractional part of non-monetary values. This is specified in the RadixSeparator element in the SDF file.
EEEE	9.9EEEE	a value in scientific notation.
G	9G999	(group separator) the character that separates groups of digits in the integer part of non-monetary values. This is specified in the GroupSeparator element in the SDF file.
L	L999	(local currency) the string representing the local currency as specified in the Currency element in the SDF file.
MI	9999MI	a trailing minus sign if the value is negative. The MI format element can appear only in the last position of a number format.
PR	9999PR	a negative value in <angle brackets>, or a positive value with a leading and trailing blank. The PR format element can appear only in the last position of a number format.
RN	RN rn	a value as Roman numerals in upper case. a value as Roman numerals in lower case. Valid values: a value between 1 and 3999.
S	S9999 9999S	a negative value with a leading or trailing minus sign. a positive value with a leading or trailing plus sign. The S format element can appear only in the first or last position of a number format.
TM	TM TM9 TME	(text minimum format) returns the smallest number of characters possible. This element is case insensitive. TM or TM9 return the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, the number is returned in scientific notation. TME returns the number in scientific notation with the smallest number of characters. You cannot precede this element with an other element. You can follow this element only with one 9 or one E (or e), but not with any combination of these.
U	U9999	(dual currency) the string that represents the dual currency as specified in the DualCurrency element in the SDF file.
V	999V99	a value multiplied by 10 to the n (and, if necessary, rounded up), where n is the number of 9's after the V.
X	XXXXX xxxxx	the hexadecimal value of the specified number of digits. If the specified number is not an integer, the function will round it to an integer. This element accepts only positive values or zero. Negative values return an error. You can precede this element only with zero (which returns leading zeroes) or FM. Any other elements return an error. If you do not specify zero or FM, the return always has one leading blank.

ANSI Compliance

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

Invocation

TO_NUMBER 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 be VARCHAR data types.

You can also pass arguments with data types that can be converted to the above data type using the implicit data type conversion rules that apply to UDFs.

Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.

For details, see “Compatible Types” in SQL External Routine Programming.

Result Type

TO_NUMBER is a scalar function whose return value data type is NUMBER.

Usage Notes

The following are examples of the TO_NUMBER syntax that Teradata supports. The examples deviate from the TO_NUMBER syntax Oracle supports. The function:

Does not supported not having a closing angle bracket for the PR format element. This example:

SELECT TO_NUMBER ('<123', '999PR')

returns NULL because the number is not in the correct format.

Allows a space at the end of the number if the PR format element is used and the number is positive. This example:

SELECT TO_NUMBER (' 123 ', '999PR')

returns 123.

Allows multiple integer digits in scientific notation. This example:

 SELECT TO_NUMBER ('12E3', '99EEEE')

returns 12000.

Allows commas in the integer portion of scientific notation. This example:

SELECT TO_NUMBER ('1,234E3', '9,999EEEE')

returns 1234000.

Validates commas when there are two arguments, even when there is no decimal in the format. This example:

SELECT TO_NUMBER ('1234', '9,999')

returns NULL because the number is not in the correct format.

Treats an empty string like any other string. This example:

SELECT TO_NUMBER ('', 'BAD Format')

returns an error, rather than NULL, because the format is not valid.

Allows the use of period (.) and comma (,) with D and G are in the same format string. This example:

SELECT TO_NUMBER ('1,234.99', '9,999D99')

returns 1234.99

Example

The following query:

SELECT TO_NUMBER ('1789.96', '9999.99');

returns the result 1789.96.

Example

The following query:

SELECT TO_NUMBER('dollar123','L999','NLS_CURRENCY=''dollar''');

returns 123.