Teradata Sql Custom Formatting

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.
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.

SEL DISTINCT format(WH_CUST_NO),format(CUST_NAME), format(BAL_AMT)
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

FORMATSample output
‘G-Z(I)D9(F)L'124,999.99 €
‘G-Z(I)D9(F)C'124,999.99 EUR
'YYYY-DDD'2008-239 (year-day of the year)
'MMMMBDD,BYYYY'August 26, 2008