SELECT Col1, CHARACTER_LENGTH(Col1) FROM Table2 ORDER BY 1; SELECT Col1, CHARACTER_LENGTH(Col1) FROM Table1 ORDER BY 1;
The 1st INSERT has 2 spaces and rest have the same number of spaces as in the middle .... e.g. for 3 we have 3 spaces then 3 as digit and then again 3 spaces!
Following is the result of 1st SELECT: Col1 Characters(Col1) 10 5 10 4 10 3 10 2 10 1 10
And following is the result of 2nd SELECT:
Col1 Characters(Col1) 0 5 6 4 5 3 4 2 3 1 2
This means what it does is, remove extra padded spaces from CHAR when inserting to VARCHAR .... which means if the column is nothing but spaces, nothing will be left of it.
What you can do is to modify SELECT as follows:
SELECT CAST(Col1 || 'a' AS VARCHAR(11)) AS C1, CHARACTER_LENGTH(CAST(Col1 || 'a' AS VARCHAR(11))) AS C2, SUBSTR(CAST(Col1 || 'a' AS VARCHAR(11)), 0, C2) as COL_TO_INSERT, CHARACTER_LENGTH(COL_TO_INSERT) FROM Table1;
Re: Inserting space as value from a char column to varchar column
Teradata implicitly trims trailing spaces when converting char(n) values to varchar(n). Assuming that this is only a problem when the source value is all spaces, you can use a case expression like the following to get the result you want:
case Col1 when ' ' then cast(' ' as varchar(10)) else cast(Col1 as varchar(10)) end
This works because the implicit type of the literal ' ' is varchar, so a conversion is avoided in that case.