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

➠ Usage of period('.'):

• Period in the format string will reflect the position of decimal point.
• Format will round off the decimal points as shown in example 2.

  
  Example 1:  SELECT CAST(453453453.435 AS FORMAT '999999,999.9999');
  Output: 453453,453.4350
  
  
  Example 2:  SELECT CAST(453453453.435 AS FORMAT '999999,999.99');
  Output: 453453,453.44    <--Round off happened here 
  

➠ '9' Metacharacter

• Displaying formtted decimal digits with extra zeros at the starting position.
• Extra zeros will be added to make size of output formatted number equal to number of metacharacter '9' specified in format string.
• In the below example 2, since eight digits of '9' metacharacter is used in the format but only six digits(before decimal point) are present in source number therefore 2 zeros are prefixed in output.

  
  Example 1: 
  SELECT CAST(512453453.435 AS FORMAT '999999,999.9999');
  Output: 512453,453.4350
  
  
  Example 2: SELECT CAST(453453.435 AS FORMAT '99999,999.9999');
  Output: 00453,453.4350
  
  
  Example 3: SELECT 453453.435  (FORMAT '99999,999.9999');
  Output: 00453,453.4350
  

➠ 'Z' Metacharacter

• Displaying formtted decimal digits with no extra zeros at the starting position.
• In the below example, even though eight digits of 'Z' metacharacter is used in the format and only six digits(before decimal point) are present in source number therefore six digits(before decimal point) are present in output.

  
  Example 1: SELECT CAST(453453.435 AS FORMAT 'ZZZZZZ,ZZZ.ZZZZ');
  Output: 453,453.4350
  
  
  Example 2: SELECT 453453.435  (FORMAT 'ZZZZZZ,ZZZ.ZZZZ');
  Output: 453,453.4350
  

➠ 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('%'):

• As shown in the below examples, percent(%) can be used to place percent in the desired location of formatted number.
• Mostly this is used to display number as percentage with percent sign as shown in example 2 & 3.

  
  Example 1:  SELECT CAST(19870606 AS FORMAT '9999%99%99');
  Output: 1987%06%06
  
  
  Example 2:  SELECT (4/5.0)* 100 (FORMAT 'ZZ9%');
  Output: 80%
  
  
  Example 3:  SELECT (4.790/5.000)* 100 (FORMAT 'ZZ9.99%');
  Output: 95.80%
  

➠ 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: ********