Truncation During Conversion | Data Types & Literals | Teradata Vantage - 17.10 - Truncation During Conversion - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

In some cases, implicit conversion can result in truncation of values without an error.

Recommendation: As a best practice, use an explicit CAST instead of relying on implicit conversions when possible.

Example: Converting to CHAR Using Teradata Conversion Syntax

Consider the following table definition:

CREATE TABLE Test1 (c1 INT, c2 VARCHAR(1));

The following two INSERT statements complete without any errors.

INSERT INTO Test1 VALUES (1, '1');
INSERT INTO Test1 VALUES (2, 2);

The following query returns two rows.

SELECT * FROM Test1;
   c1      c2
-------------
    1       1
    2               <<<< Note that the value inserted in c2 is a blank

In the second INSERT statement, the number 2 was implicitly converted to CHAR using Teradata conversion syntax (that is, not using CAST). The process is as follows:

  1. Convert the numeric value to a character string using the default or specified FORMAT for the numeric value. Leading and trailing pad characters are not trimmed.
  2. Extend to the right with pad characters if required, or truncate from the right if required, to conform to the target length specification.

If non-pad characters are truncated, no string truncation error is reported.

The conversion right-justifies the number, but takes the first byte of the result which is a single blank character. For more information about numeric to character conversions, see Numeric-to-Character Conversion.