ASCII Input Conversion

Truncation of Data for VARCHAR and CHAR Columns

If the width of the input column is greater than the width of the destination column, SAP Sybase 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 SAP Sybase 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 ASCII conversion option works with the SAP Sybase IQ data types. The example inserts the data from the flat ASCII file shipinfo.t into the SAP Sybase 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