Sybase 12.4.2 manual 209

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.

	Table 5-6: Input file conversion example
shipinfo.t			lineitem
column	format	width	column	datatype	width
l_shipmode	CHAR	15	l_shipmode	VARCHAR	30
l_quantity	ASCII	8	l_quantity	INT	4

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

209