Implicit conversion can cause truncation of values without an error. Teradata recommends using an explicit CAST instead of relying on implicit conversions.
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 <<<< 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:
- 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.
- 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. See Numeric-to-Character Conversion.