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

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.