Usage Notes - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantageā„¢ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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 support not having a closing angle bracket for the PR format element. This example returns NULL because the number is not in the correct format.:
    SELECT TO_NUMBER ('<123', '999PR') 
  • Allows a space at the end of the number if the PR format element is used and the number is positive. This example returns 123:
    SELECT TO_NUMBER (' 123 ', '999PR') 
  • Allows multiple integer digits in scientific notation. This example returns 12000:
    SELECT TO_NUMBER ('12E3', '99EEEE') 
  • Allows commas in the integer portion of scientific notation. This example returns 1234000:
    SELECT TO_NUMBER ('1,234E3', '9,999EEEE') 
  • Validates commas when there are two arguments, even when there is no decimal in the format. This example returns NULL because the number is not in the correct format:
    SELECT TO_NUMBER ('1234', '9,999') 
  • Treats an empty string like any other string. This example returns an error, rather than NULL, because the format is not valid:
    SELECT TO_NUMBER ('', 'BAD Format') 
  • Allows the use of period (.) and comma (,) with D and G are in the same format string. This example returns 1234.99:
    SELECT TO_NUMBER ('1,234.99', '9,999D99')