Inserting Integer into Varchar

Tools & Utilities
Enthusiast

Inserting Integer into Varchar

Hi All,

I have a table with col datatype varchar(5)

When I try to insert an integer value which is moe then 1 byte i.e. either smallint or higher it truncates the value whereas if I explicitly cast the value into varchar(5) then it allows me to insert e.x

Insert into table value (127) o/p is 127
Insert into table value (128) o/p is 12
Insert into table value sel cast(128 as varchar(5)) o/p is 128.

Can anyone let me know why it's happeneing as varchar(5) should be able to store upto 5 digits.

Any help would be appreciated.

Regards
Ashish
3 REPLIES
Enthusiast

Re: Inserting Integer into Varchar

whenever you try to convert an numeric data type to character type, teradata would internally apply the formating associated with the numeric type to convert it to character data.

127 in TD defaults to type BYTEINT

SELECT TYPE(127), FORMAT(127);

Type(127) Format(127)
BYTEINT -(3)9

Where as 128 is larger than BYTEINT's max value, so it becomes SMALLINT, and the format changes too.

SELECT TYPE(128), FORMAT(128)

Type(128) Format(128)
SMALLINT -(5)9

As you can see the data types and their formats are different for these two values.

So for 127 it applied formating -(3)9
and you got <1 SPACE>127 (Total 4 characters)

where as for 128, the formating for SMALLINT resulted in
<3 SPACES>128 (Total 6 characters)

And since it's VARCHAR(5), you got only <3 SPACES>12 in the table. (ie first 5 character positions)

You can probably do something like this

INSERT INTO MYTABLE(COL1) VALUES(TRIM(128));

But if you are using the column to store integers, why don't you declare it to be a integer to begin with ?

Integers would be a lot easier to store and manipulate.
Enthusiast

Re: Inserting Integer into Varchar



Thanks a lot but I am still not absolutely clear.

Can you brief me ab format –(3)9 and –(5)9

Moreover if I consider 4 digit values like 9999 ,1294 my format and type would be smallint

Type(9999) Format(9999)
SMALLINT -(5)9

In this scenario why its considering <2 space> 999 and <2space>129.

And if I consider 5 digit smallint values like 22222,11111 again the format and type would be same but it will truncate only last value i.e. <1 space>2222 and <1 space>1111.

Any help would be appreciated.

Regards
Ashish

Junior Contributor

Re: Inserting Integer into Varchar

Hi Ashish,
those format are COBOL-style,
e.g. '–(5)9' is the same as '-----9' which means 5 digits plus a floating sign padded with blanks. This is the default format for a smallint.

Teradata-style typecasts [9999 (char(6))] are right-aligned within that format, ANSI typecasts [cast(9999 as char(6)] left-aligned.
Automatic typecasts are always Teradata-style, i.e. right aligned:

9999 -> ' 9999'
129 -> ' 129'

ANSI:
9999 -> '9999 '
129 -> '129 '

You'll find all those details in the "SQL Reference: Data Types and Literals" manual.

So if you want it left-aligned switch to CAST(9999 as VARCHAR(6)). Don't use CAST(9999 as CHAR(6)) if your target column is varchar, because trailing blanks will be stored in VARCHARs.

But i'd use TRIM(9999) so you don't have to think about leading or trailing blanks :-)

Dieter