16.20 - Usage Notes - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Types and Literals

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

Before processing begins, the numeric description is scanned for a FORMAT phrase, which is used to determine the radix separator, group separator, currency sign or string, signzone (S), or implied decimal point (V) formatting.

Conversion is performed positionally, character by character, from left to right, until the end of the number.

Only all-numeric character strings can be converted from character to numeric formats. For example, you can convert the character strings ‘US Dollars 123456’ or ‘123456’ to the integer value 123456, but you cannot convert the string ‘EX1AM2PL3E’ to a numeric value.

The following list shows the steps for converting character type data to numeric. Note that you cannot convert a character_expression of GRAPHIC character type to numeric.

Conversion is performed stage by stage, without returning to a previous stage; however, stages can be skipped.

  1. Leading pad characters are ignored. Trailing pad characters are ignored, except for signed zoned decimal input.

    Embedded spaces are only allowed according to the following rules:

    • If the current SDF file defines the group separator as a space, then the character string can include spaces to separate groups of digits to the left of the radix separator, according to the grouping rule defined by GroupingRule or CurrencyGroupingRule.
    • If the current SDF file defines the radix separator as a space, then the character string can include one space as the radix character.
    • If the FORMAT phrase contains a currency formatting character, such as N, and the matching currency string in the SDF file, such as CurrencyName, contains a space, the character string can include spaces as part of that currency string.
  2. The sign (+ or -) is saved as part of the number. A mantissa sign may appear before the first digit in the string, or after the last digit in the string. An exponent sign may appear with a preceding mantissa sign.
  3. The currency sign is ignored if it matches the FORMAT. A currency string is ignored if it matches the FORMAT. Only one currency is allowed in the string.
  4. Digits are saved as the integral, fractional, or exponent part of the number, depending on whether the radix or the letter E has been parsed.
  5. Separators are ignored, unless they match the radix specified in the FORMAT.

    If a separator matches the radix specified in the FORMAT, the location is saved as the beginning of the fractional part of the number. V marks the fractional component for implied decimals.

    The allowance of currency and separators is a non-ANSI Teradata extension of character to numeric conversion.

  6. Embedded dashes (between digits) are allowed, unless the number is signed or includes a radix, currency, or exponent.
  7. The letter E is saved as the beginning of the exponent part of the number. One space is allowed following an E.
  8. The exponent sign (+ or -) is saved.
  9. The exponent digits are saved. A sign character cannot appear after any exponent digit.