In article <3lc91o$p4k@insosf1.infonet.net>, bptech@worf.infonet.net () writes:
|> larson@sled.gsfc.nasa.gov (Teresa A Larson) writes:
|> 
|> >In article <3l7h33$rco@insosf1.infonet.net>, bptech@worf.infonet.net () writes:
|> >|> Why did Sybase decide that any spaces at the end of
|> >|> a varchar field should be truncated?  I would think
|> >|> that if I inserted "123456789     ", that I would 
|> >|> get this back.  Is there some way to turn this behavior
|> >|> off?
|> 
|> >You could create the column as CHAR(n) instead of VARCHAR(n) which 
|> >would make the column fixed length which is the behavior you are 
|> >looking for.
|> 
|> My understanding is that if I have a char(255) and insert a line
|> that contains 90 characters, then it is padded with spaces at the
|> end of the line.  This is not what I want.  I will probably use
|> the type IMAGE or TEXT, but I still question why Sybase created
|> varchar fields like this.

I ask why about a lot of things with almost any tool or product I
use.  But, I guess I wasn't very clear with my earlier response.
My apologies.  I hope this helps.

Columns of type VARCHAR are not padded with spaces.  Columns of type
CHAR are not padded if they allow nulls; they are fixed length (padded)
if they don't allow nulls.  This is because fixed-length columns that
allow nulls are internally converted to variable-length columns.
For example:

create table mytab (col1 varchar(20) null, col2 char(20) null, col3 int)
and insert some values                                   ^^^^

insert mytab values ('5 blank spaces     ', '5 blank spaces     ', 1)
insert mytab values ('0 blank spaces', '0 blank spaces', 2)
insert mytab values ('short', 'short', 3)

1> select '*' + col1 +'*', '*' + col2 + '*', col3 from mytab
2> go
                                               col3        
 ---------------------- ---------------------- ----------- 
 *5 blank spaces*       *5 blank spaces*                 1 
 *0 blank spaces*       *0 blank spaces*                 2 
 *short*                *short*                          3 

(3 rows affected)

create table mytab (col1 varchar(20) null, col2 char(20), col3 int)
and insert same values

1> select '*' + col1 +'*', '*' + col2 + '*', col3 from mytab
2> go
                                               col3        
 ---------------------- ---------------------- ----------- 
 *5 blank spaces*       *5 blank spaces      *           1 
 *0 blank spaces*       *0 blank spaces      *           2 
 *short*                *short               *           3 

(3 rows affected)


There are limitations when using TEXT and IMAGE types (see the section
"Text and Image Datatypes" in Vol 1 of the Server Reference Manual).
For example, you can't use a TEXT column in a WHERE clause.
Also, the minimum amount of space used to store TEXT type data is 2K
once it has been assigned a value or if it's doesn't allow nulls.
That would be a lot of wasted space if there's only 90 characters
of data.

                                Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation                            |
| NASA/GSFC Code 933.0                        voice:  (301) 286-7867   |
| Greenbelt, Maryland  20771                  fax:    (301) 286-1777   |
| Teresa.Larson@gsfc.nasa.gov                                          |
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
                        Standard disclaimer ...