How to get a Null value instead of a backslash(\) when unloading an empty varchar string.

Problem(Abstract)

This article tells you how to get a Null value(||) instead of a backslash (|\|) When you unload an empty varchar string.

Symptom

When executing SQL like below:

Example:
unload to x.unl select tabname, "" from systables where tabname = "tab1" 

The output file contains:
tab1|\ |

Cause

It is expected behavior as NULL cannot be represented by "", because IBM Informix considers this an empty string internally.

Resolving the problem

Use one of the following workarounds to get || instead of |\ | :

1) SELECT NULL::CHAR FROM table


or

2) SELECT CAST(NULL AS CHAR) FROM table



This simply defines the data type of the result set to return to the client.

Example:

unload to tab1.unl select tabname,CAST(NULL AS CHAR) from systables where tabid = 99;

1 row(s) unloaded.

> !cat t1.unl
VERSION||

or

> unload to t1.unl select tabname,NULL::CHAR from systables where tabid = 99;

1 row(s) unloaded.

> !cat tab1.unl

VERSION|| 

http://www-01.ibm.com/support/docview.wss?uid=swg21637592