Default spaces

UDA

Default spaces

I am new to tera data. I have a doubt, that..

If a field size is 25 and the value in that is only of lenght 10 the remaining 15 places will be blank spaces?

Pls help me?
6 REPLIES
Enthusiast

Re: Default spaces

it depends on how you define the field.
if it is defined as field1 char(25) then the field lenght is 25 no matter what data you put in it.
if you define the field as varchar(25) then the field lenght is the size of the text that you put in it. It does not place blanks.

Re: Default spaces

Thanks a lot.
Teradata Employee

Re: Default spaces

Not quite accurate, and a frequent misconception. Trimming trailing spaces from VARCHAR doesn't happen by magic - you need to be careful on the client side and/or use the TRIM function:

CREATE TABLE showVarchar( aKey INTEGER,
vText VARCHAR(25)
);

CREATE TABLE showVarchar2( aKey INTEGER,
vText VARCHAR(25)
);

insert into showVarchar values (1, 'Short');
insert into showVarchar values (2, 'Long ');
insert into showVarchar values (3, ' ');
insert into showVarchar values (4, '');

insert into showVarchar2 select * from ShowVarchar;

select aKey, vText, CHARACTERS(vText) from showVarchar2 order by aKey;

aKey vText Characters(vText)
1 Short 5
2 Long 14
3 20
4 0

Enthusiast

Re: Default spaces

My understanding of Varchar has been that no trailing spaces where stored
also, and that Chars function was specially suited to Varchar for this reason.
So I reran you query

results: aKey vText Characters(vText)
1 Short 5
2 Long 5
3 1
4 0

SQL assistant Ver 6.2.0

RELEASE V2R.05.01.02.23
VERSION 05.01.02.41

Any Ideas why?

Tbob
Enthusiast

Re: Default spaces

Tbob That is true..
I am not sure how some one else gets different results,
I ran the same queries that you gave and got the results as expected

select aKey, vText, CHARACTERS(vText) from devdata.feroz_showVarchar2 order by aKey;

aKey vText Characters(vText)
1 Short 5
2 Long 5
3 1
4 0

and then i inserted another row in the showVarchar table

insert into devdata.feroz_showVarchar values (5, 'Ferozbbbbbbbbbb');
(the b above is to represent the blanks, the actual value was 'Feroz followed by 10 spaces')

and then i inserted this row into Showvarchar2 table

insert into devdata.feroz_showVarchar2
select * from devdata.feroz_showVarchar
where akey = 5

then i ran the character query again.
select aKey, vText, CHARACTERS(vText) from devdata.feroz_showVarchar2 order by aKey;

aKey vText Characters(vText)
1 Short 5
2 Long 5
3 1
4 0
5 Feroz 15

This is exactly what i was expecting.
Fred I am not sure how you go those results, has that got something to do with version??
Even then the concept of varchar and char should not change.

by the way i was running this in sql assistant 6.2 and we are on Teredata V2R5.

Thanks

Teradata Employee

Re: Default spaces

In my test example, I included ten spaces after the word Long for this statement:

insert into showVarchar values (2, 'Long ');

Somehow it appears to have been truncated to a single blank in the post. But notice your tests still show 5 characters not 4 for this string - proving my point that the trailing space(s) are stored, though a bit less obvious than I intended.