SAP IQ: Distinguish between blank and empty string

[kazuootani]

IQ 16.0 on Windows

When load table is performed with LOAD_ZEROLENGTH_ASNULL = 'OFF' (default), zero-length value in input file becomes empty string (= null string having binary value 0x000000..) in char/varchar columns.

Please see the following senario:

1. create a table having varchar column

  create table t1 (a varchar(5) null)

2. create a text file with three lines

  abc
        --> second line has no value; it is zero-length
  12345 

3. load the file created by 2 to the table created by 1

  load table t1 (a) from 't1.txt'
  format bcp
  row delimited by '\x0d\x0a'
  quotes off escapes off

4. Add another row that has a blank

  insert into t1 values (' ')

5. select the contents of the table

  select a, convert(binary(5),a) from t1

 a     a 
 ----- ----------------
 abc   0x6162630000
       0x0000000000  --> empty string
 12345 0x3132333435
       0x2000000000  --> blank 

6. Tried to retrieve an empty string

  select a, convert(binary(5),a) from t1 where a=0x00

  a     a
  ----- ----------------
        0x0000000000  --> empty string
        0x2000000000  --> blank

Not only empty string but blank row is also returned.
Is this an expected behavior?
I want only empty string and blank is not necessary.

How do I distinguish between BLANK (0x20) and empty string (0x00)?
I would like to change empty string to NULL, but don't want to change BLANK.

Regards,
Kazuo Otani

[kazuootani]

I answer to my own question.

I found similar topic at KBA:
2150485 - Empty string and a space character are treated as equal - SAP IQ

In SAP IQ, empty string and blank are treated as equal.

Suppose you have a table containing both empty string and blank in varchar column and you want to distinguish them.
Check data length.

To get empty string,

where datalength(<colname>) = 0

To retrieve blank only,

where <colname> = ' ' and datalength(<colname>) > 0

If you would like to change empty string to NULL and leave blank unchanged,
I suggest the SQL:

  update <tablename> set <colname> = NULL  
  where datalength(<colname>) = 0

Regards,
Kazuo Otani