Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 229 / 536

CHAPTER 5 Moving Data In and Out of Databases
209
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
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
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