Truncation During Conversion

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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.