The LOAD TABLE STRIP ON keyword has been deprecated in this release. The following example shows the results of a query using the LOAD TABLE STRIP RTRIM keyword instead of the STRIP ON keyword.
LOAD TABLE QUOTES option example Consider a table defined as:
CREATE TABLE t1 (c1 INT, c2 VARCHAR(20), c3 VARCHAR(20))
with the following input data:
1, apple , fruit1 , 2, "banana" , "fruit2", 3, " pear ", " fruit3 ",
The result of loading this data is displayed by running the following query:
SELECT c1, c2, c3, LENGTH(c2), LENGTH(c3) FROM t1
Given the values of the QUOTES and STRIP options used during the LOAD TABLE command, the following table displays the result of the query above, with each result enclosed by ‘<’ and ‘>’:
LOAD TABLE options |
Results of SELECT c1, c2, c3, LENGTH(c2), LENGTH(c3) FROM t1 |
|||||
|---|---|---|---|---|---|---|
QUOTES |
STRIP |
c1 |
c2 |
c3 |
length(c2) |
length(c3) |
ON |
RTRIM |
<1> |
<apple> |
<fruit1> |
<5> |
<6> |
<2> |
<banana> |
<fruit2> |
<6> |
<6> |
||
<3> |
< pear > |
< fruit3 > |
<6> |
<8> |
||
ON |
OFF |
<1> |
<apple > |
<fruit1 > |
<6> |
<7> |
<2> |
<banana> |
<fruit2> |
<6> |
<6> |
||
<3> |
< pear > |
< fruit3 > |
<6> |
<8> |
||
OFF |
RTRIM |
<1> |
< apple> |
< fruit1> |
<6> |
<7> |
<2> |
< “banana”> |
< “fruit2”> |
<9> |
<9> |
||
<3> |
< “ pear ”> |
< “ fruit3 ”> |
<9> |
<11> |
||
OFF |
OFF |
<1> |
< apple > |
< fruit1 > |
<7> |
<8> |
<2> |
< “banana” > |
< “fruit2”> |
<10> |
<9> |
||
<3> |
< “ pear ”> |
< “ fruit3 ”> |
<9> |
<11> |
||
Notes on the results:
With QUOTES ON and STRIP RTRIM, both leading space and trailing space for the non-enclosed field c2 row 1 are trimmed.
With QUOTES ON and STRIP OFF, only the leading space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES OFF and STRIP RTRIM, only the trailing space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES OFF and STRIP OFF, neither leading space nor trailing space for the non-enclosed field c2 row 1 is trimmed.
With QUOTES ON and STRIP RTRIM, both leading space and trailing space within quotes for the enclosed fields c2 and c3 row 3 are NOT trimmed.
