Filter
Top Products
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