Formatting numbers in Teradata
Every column in a Teradata table has a default format defined.
You can use the FORMAT function to read this format.
Example:
Let’s consider the following three columns in one of the Teradata tables:
- Customer number (WH_CUST_NO) - defined as INTEGER
- Customer name (CUST_NAME) - VARCHAR(200)
- Balance amount (BAL_AMT) - defined as DECIMAL (15,2)
The SQL printed below will print the teradata formatting definitions for the columns above.
FROM gg_cli
SQL Result:
Format(WH_CUST_NO) Format(CUST_NAME) Format(BAL_AMT)
-(10)9 X(200) --------------.99
SDF symbols
The formatting definitions in Teradata are stored in a SDF file (Specification for Data Formatting) which can be accessed with a TDLocaldef tool.
It is usually used to set currency symbols and options, zero-suppression, formatting numbers and signed numbers.
Symbols that may be defined in the SDF:
G – group separator. Must appear as the first character in a format string. Usually a space or a comma
D – decimal separator. Usually a dot or a comma
F – number of chars needed to display the decimal part of a numeric value
I - number of chars needed to display the INTEGER number
Z – zero-suppressed decimal digit
L – currency symbol
C – iso currency symbol
SDF symbols may be used in the FORMAT string, however they can not be mixed with the standard formats (a dot for instance).
A list of useful Teradata number formats
| FORMAT | Sample output |
| ‘Z99,999.99’ | 124,999.99 |
| ‘G-(10)D9(2)' | 124,999.99 |
| ‘G-Z(I)D9(F)L' | 124,999.99 € |
| ‘G-Z(I)D9(F)C' | 124,999.99 EUR |
| '999-99-99-999' | 124-56-78-000- |
| ’-(13)’ | 125000 |
| ’-(10)D9(3)’ | 124999,990 |
| 'YYYY-DDD' | 2008-239 (year-day of the year) |
| 'MMMMBDD,BYYYY' | August 26, 2008 |