Strange behavior while moving data from Integer columns to Varchar columns

Database
Enthusiast

Strange behavior while moving data from Integer columns to Varchar columns

Recently I came to know about strange behavior of the Teradata 

The case is that when we are moving data FROM Integer field of one table to varchar field of second table then a blank is inserted into the table instead of actual number

Have replicated the same scenario using dummy tables 

==>Dummy table 1 with Id column as integer

 CREATE MULTISET TABLE Dummy_Table ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Id INTEGER,

      Name VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( Id ); 

INSERT INTO Dummy_table VALUES(1,'Pradeep');

SELECT * FROM Dummy_Table;

         Id  Name

-----------  ----------------------------------------------------------------------------------------------------

          1  Pradeep

==> Dummy table 2 with Id column as Varchar

CREATE MULTISET TABLE Dummy_Table_Varchar ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Id VARCHAR(10),

      Name VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

 PRIMARY INDEX ( Id );

==> Inserting from Dummy table 1 to Dummy table 2

INSERT INTO Dummy_Table_Varchar(id,name) SELECT id,name FROM Dummy_table;

 *** Insert completed. One row added. 

==> Checking data in Dummy table 2 with Id column as varchar

SELECT * FROM Dummy_Table_Varchar;

Id          Name

----------  ----------------------------------------------------------------------------------------------------

            Pradeep

SELECT * FROM Dummy_Table_Varchar WHERE id IS NULL;

 *** Query completed. No rows found. 

 *** Total elapsed time was 1 second.

SELECT CHARACTER_LENGTH(id),id,name FROM Dummy_Table_Varchar;

Characters(Id)  Id          Name

--------------  ----------  ----------------------------------------------------------------------------------------------------

             0              Pradeep

SELECT CASE WHEN TRIM(id)='' THEN null ELSE id END Id ,name FROM Dummy_Table_Varchar;

Id          Name

----------  ----------------------------------------------------------------------------------------------------

?           Pradeep

==> Inseting data by manual typecasting

INSERT INTO Dummy_Table_Varchar(id,name) SELECT cast(id as varchar(10)),name FROM Dummy_table;

select * from Dummy_Table_Varchar;

Id          Name

----------  ----------------------------------------------------------------------------------------------------

1           Pradeep

            Pradeep

We can see that zero character length string is inserted in the table instead of number if explicit typecast is not done

I want to undestand why Teradata is not supporting auto type-casting in this case

Thanks,

Pradeep

3 REPLIES
Enthusiast

Re: Strange behavior while moving data from Integer columns to Varchar columns

Hi,

It is not the strange behaviour rather it is default behaviour. An when an intger is converted to varchar, it requires 11 characters for representation. So when you try to insert the integer into varchar it is converted and trimmed the from 11 char converted value to 10 char. the value 1 comes at most right and is trimmed off.

So in order to avoid this truncation, you can either cast the id explicitly to VARCHAR(10). Or use the VARCHAR(11) data type while defining the column.

Hope it helps!

Khurram
Enthusiast

Re: Strange behavior while moving data from Integer columns to Varchar columns

Thanks Khurram for the input

Still I am curious about why teradata is not supporting this implicitly

Enthusiast

Re: Strange behavior while moving data from Integer columns to Varchar columns

I dont know why I am unable to paste the code, but In fact Teradata is implicitly converting the integer to Varchar, it happens as follows:

Integer 1 is convertes as ----------1, so while insertion to varchar(10), the 1 is trimmd. The dashes are for empty characters.

Khurram