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