Sybase 12.4.2 Benutzerhandbuch

CHAPTER 5    Moving Data In and Out of Databases

Truncation of data for 
VARCHAR and CHAR 
columns

If the width of the input column is greater than the width of the destination 
column, Adaptive Server IQ truncates the data upon insertion. If the width of 
the input data is less than the width of the destination column, for 

CHAR

 or 

VARCHAR

 data types Adaptive Server IQ pads the data with spaces in the table 

upon insertion.

Variable width inserts to a 

VARCHAR 

column will not have trailing blanks 

trimmed, while fixed width inserts to a 

VARCHAR 

column will be trimmed. For 

example, assume that you are inserting into column 

varcolumn

 in a table called 

vartable

. The following would constitute a fixed-width insert, where the value 

would not be trimmed because you explicitly say to include the two blanks 
(indicated by __ here):

INSERT INTO vartable VALUES (’box__’)

If instead you inserted the same value from a flat file using delimited input, it 
would be a variable-width insert, and the trailing blanks would be trimmed.

The following table illustrates how the 

ASCII

 conversion option works with the 

Adaptive Server IQ data types. The example inserts the data from the flat 
ASCII file shipinfo.t into the Adaptive Server IQ table 

lineitem

 and summarizes 

the content and format of the input data and the table.

For the 

l_shipmode

 column, you insert ASCII data into an ASCII column (that 

has a 

VARCHAR

 data type). Notice the width of the two columns is different. 

In order for the insert on this column and the subsequent 

l_quantity

 column to 

be correct, you specify the width of the 

l_shipmode

 column so the correct 

amount of input data is read at the correct position. 

For the 

l_quantity

 column, you are inserting ASCII data into a binary column 

(

INT

 data type). In order for the insert on this column to be correct, you must 

convert the input data into binary and indicate the width of the input column.

The command for this is shown in the following UNIX example.

LOAD TABLE lineitem(

    l_shipmode ASCII(15),

    l_quantity ASCII(8),

FILLER(1))

FROM ’/d1/MILL1/shipinfo.t’

PREVIEW ON

l_shipmode

CHAR

15

l_shipmode

VARCHAR

30

l_quantity

ASCII

8

l_quantity

INT

4