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: Valid values for param are: 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. |
. (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 |
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 |
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 |
(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 |
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:
SELECT TO_NUMBER ('<123', '999PR')
returns NULL because the number is not in the correct format.
SELECT TO_NUMBER (' 123 ', '999PR')
returns 123.
SELECT TO_NUMBER ('12E3', '99EEEE')
returns 12000.
SELECT TO_NUMBER ('1,234E3', '9,999EEEE')
returns 1234000.
SELECT TO_NUMBER ('1234', '9,999')
returns NULL because the number is not in the correct format.
SELECT TO_NUMBER ('', 'BAD Format')
returns an error, rather than NULL, because the format is not valid.
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.