For data involving string types such as CHAR or VARCHAR, make sure to distinguish between NULL and empty fields when reading and writing rows in CSV style. The Apply table operator uses the following convention:
- Any field (not just string-like fields) with an unquoted empty string is interpreted as NULL. For example, a,b,,d. The third field is encoded as NULL.
- A field with a quoted empty string is an empty string. For example a,b,"",d where the QUOTECHAR is a single double-quote. The third field must be a type that contains an empty string as a valid value such as VARCHAR or CHAR. Otherwise, an error is returned.
Consider this row:
a,b,,d
If the third column is nullable, the User Process can read and interpret the third field of the the row as NULL. Conversely, a result row where a field is unquoted and empty is interpreted as a NULL value.
Now consider this row:
a,b,"",d
The User Process interprets the third field as an empty string. Conversely, an empty string in a field for a result row must write a quoted empty string using the QUOTECHAR.
Whitespace is part of the field according to 2.4 in RFC4180.