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:
- 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. For more information about numeric to character conversions, see Numeric-to-Character Conversion.