Teradata Number Formatting

Teradata Number Formatting

Teradata provides a way to display numeric values in the desired format. Multiple characters (Metacharacters) are available in Teradata which can be used along with FORMAT keyword to display numeric values in the required format.

Below tables show most of the metacharacters that can used with FORMAT keyword.

MetaCharacters

Description / Functionality

  9

Display decimal digits in specified format but zeros will not be suppressed

  Z

Display decimal digits in specified format and zeros will be suppressed

  $

Display dollar($) sign in front of the formatted number (example: 34343.02 to $34343.02)

  ,

Display comma(,) at the specifed position of formatted number (example: 343234343.02 to 343,234,343.02)

  -

Display hyphen(-) at the specifed position of formatted number (example: 4083434448 to 408-3434-448)

  %

Display percent(%) sign at the specifed position of formatted number (example: 79.02 to 79.02%)

  /

Display slash(/) sign at the specifed position of formatted number (example: 20181212 to 2018/12/12)

  .

Period(.) sign reflect the position of decimal point

Note: IDE/JDBC Tools may not show formatted number properly if output is not specifically casted to VARCHAR/CHAR.


General Syntax
Syntax 1:  source_number/number_column (FORMAT 'format_string')


Syntax 2:  CAST(source_number/number_column  AS FORMAT 'format_string')



All the below examples can be used on any type of numeric columns (INTEGER, DECIMAL etc).

➠ Usage of period('.'):

➠ '9' Metacharacter

➠ 'Z' Metacharacter

➠ Usage of comma(','): As shown in the below examples, comma(,) can be used to place comma in the desired location of formatted number.
Example 1:  SELECT CAST(453453453.435 AS FORMAT '999,999,999.99');
Output: 453,453,453.44


Example 2: SELECT CAST(453453453.435 AS FORMAT '999999,999.99');
Output: 453453,453.44


Example 3: SELECT 453453453.435  (FORMAT '999999,999.99');
Output: 453453,453.44


➠ Usage of hyphen('-'): As shown in the below examples, hyphen(-) can be used to place hyphen in the desired location of formatted number. Popularly used for formatting phone numbers.
Example 1:  SELECT CAST(4534534532 AS FORMAT '9999-999-999');
Output: 4534-534-532


Example 2:  SELECT 4534534532  (FORMAT '9999-999-999');
Output: 4534-534-532



➠ Usage of slash('/'): As shown in the below examples, slash(/) can be used to place slash in the desired location of formatted number.
Example 1:  SELECT CAST(19870606 AS FORMAT '9999/99/99');
Output: 1987/06/06


Example 2:  SELECT 19870606 (FORMAT '9999/99/99');
Output: 1987/06/06


➠ Usage of percent('%'):

➠ Usage of dollar('$'): Mostly this is used to display number as amount with dollar sign in front. Below example displays the same.
Example 1:  SELECT 34343 (FORMAT '$999,99.99');

Output: $343,43.00


Careful! always ensure formatting string is bigger than the source number else format will return asterisk instead of formatted number.
In the below example, format string is of size 4 (9,999)(before decimal point) and source number is of size 5 (53453). Since the size of format string is smaller than source number, output returned is asterisks.
Example 1:  SELECT CAST(53453.435 AS FORMAT '9,999.99');

Output: ********