Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

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')