Skip to Content

SAP IQ: Distinguish between blank and empty string

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • 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

    Regards,
    Kazuo Otani

    Add comment
    10|10000 characters needed characters exceeded