|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/22/2006 1:31:00 PM
Posts: 2,
Visits: 1
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 12:54:51 PM
Posts: 116,
Visits: 20
|
|
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.
Feroz Shaik
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/22/2006 1:31:00 PM
Posts: 2,
Visits: 1
|
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Yesterday @ 6:11:07 PM
Posts: 331,
Visits: 533
|
|
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
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 11/11/2008 8:14:01 PM
Posts: 58,
Visits: 10
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 12:54:51 PM
Posts: 116,
Visits: 20
|
|
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
Feroz Shaik
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Yesterday @ 6:11:07 PM
Posts: 331,
Visits: 533
|
|
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.
|
|
|
|