Skip to Content

SAP IQ: Distinguish between blank and empty string

Aug 21, 2017 at 09:09 AM


avatar image

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

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

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.

Kazuo Otani

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Kazuo Otani Aug 23, 2017 at 05:57 AM

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

Kazuo Otani

10 |10000 characters needed characters left characters exceeded