FORMAT while CREATING and SELECTING in Teradata

Using FORMAT in CREATE TABLE

When the FORMAT phrase is used in the CREATE TABLE statement, as follows:

CREATE TABLE Test1 (Col1 DECIMAL(4) FORMAT ’9999S’);


then zoned input character strings can be loaded with standard INSERT statements, whether the data is defined:

INSERT INTO Test1 (Col1) VALUES (’123J’);


Subsequently, a simple select, such as:

SELECT Col1 FROM Test1;



output:
Col1
------
123J



Using Another FORMAT in the SELECT Statement:

To override an attached format, another FORMAT phrase is needed in the retrieval statement.
Using the preceding table, one of the two following statements must be used to retrieve the numeric value:

SELECT Col1 (FORMAT ’+9999’) FROM Test1;
or
SELECT CAST (Col1 AS INTEGER) FROM Test1;


output:
Col1
-------
-1231

..