17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

To convert a numeric type value to a character string, the character description must contain a data type declaration. A FORMAT phrase, by itself, cannot be used to convert a numeric type value to a character type value. The phrase only controls how to display the resultant value.

If the character description does not include a FORMAT phrase, then the format of the original numeric value determines how to display the data.

The Teradata conversion syntax form of numeric-to-character conversion uses explicit or default FORMATs to convert to a character representation. It then truncates or extends with pad characters, depending what length the character string dictates. This can lead to a loss of significance.

Attempting to convert from a numeric type to a character type that uses a GRAPHIC server character set generates an error.

As a general rule, you should store numbers as numeric data, not as character data. For example, a table is created with the following code:

   (job_code CHAR(6) PRIMARY KEY
   ,description CHAR(70) );

Subsequently, the following query is made:

SELECT job_code, description
FROM job
WHERE job_code = 1234;

The problem here is that ‘1234’, ‘1234’, ‘01234’, ‘001234’, ‘+1234’, and so on, are all valid character representations of the numeric literal value, and the system cannot tell which value to use for hashing. Therefore, the system must do a full table scan to convert all job_code values to their numeric equivalents so that it can do the comparisons.