Single Space Handling for ABAP in SAP HANA

ABAP_VARCHAR_MODE

An empty string ('') has been stored in database tables as a single space (' ') in ABAP. Program rely on special handling of the empty string. For example, CoLA = ' ' is in use to see whether ColA store the empty string or not.

In order to resolve this spacing issue, the "ABAPVARCHARMODE" was introduced for SAP HANA, it replaces ' ' with '' in the SQL strings. Therefore both,an empty string and a single space string (' '), are treated like empty string whenever "ABAPVARCHARMODE" is true. But, if "one space" is used in expression the result will be differ depending on if the "ABAPVARCHARMODE" is turned off or on.

As a method for overcoming this problem char(32) can be used instead of one space (' ') in expressions and the single space should be preserved while inserting one space from ABAP.

Some of the example are shown below:

First Set ABAPVARCHARMODE to TRUE

From dummy select session_context('ABAPVARCHARMODE') ;

now set 'ABAPVARCHARMODE' = 'true';

select session_context('ABAPVARCHARMODE') from dummy;

Setting up a test table and inserting values to the test

drop table t1;

create table t1(k integer primary key, s varchar(20), v VARCHAR(60));

insert into t1 values (1, '', 'VARCHARMODE:TRUE, EMPTY SPACE IS INSERTED');

insert into t1 values (2, ' ', 'VARCHARMODE:TRUE, ONE SPACE IS INSERTED');

insert into t1 values (3, ' ', 'VARCHARMODE:TRUE, TWO SPACES ARE INSERTED' );

insert into t1 values (4, 'date time', 'VARCHARMODE:TRUE, date time STRING Inserted');

insert into t1 values (5, CHAR(32), 'VARCHARMODE:TRUE, CHAR(32) IS INSERTED instead of one space');

Reset ABAPVARCHARMODE to FALSE and insert "one space" to test

set 'ABAPVARCHARMODE' = 'FALSE';

select session_context('ABAPVARCHARMODE') from dummy;

insert into t1 values (6, ' ', 'VARCHARMODE:FALSE, ONE SPACE IS INSERTED');

Set ABAPVARCHARMODE to TRUE

set 'ABAPVARCHARMODE' = 'TRUE';

select session_context('ABAPVARCHARMODE') from dummy;

Check the result with the following queries

select v, k, s, '[' || s || ']', length(s), locate(s, ' '), locate(s, char(32)), substring(s, 0, locate(s, ' ') ), substring(s, 0, locate(s, char(32)) ) from t1;

select v, k, s, '[' || s || ']', length(s) from t1 where s = ' '; -- Returns row 1 and 2

select v, k, s, '[' || s || ']', length(s) from t1 where s = char(32); -- Returns row 5 and 6

Check behaviour of expressions

First set 'ABAPVARCHARMODE' = 'TRUE';

SELECT 'TEST' || LPAD('TEST', 10, ' ') FROM DUMMY; -- Returns 'TESTTEST'

SELECT 'TEST' || LPAD('TEST', 10, char(32)) FROM DUMMY; -- Returns 'TEST TEST'

Now set 'ABAPVARCHARMODE' = 'FALSE';

SELECT 'TEST' || LPAD('TEST', 10, ' ') FROM DUMMY; -- Returns 'TEST TEST'

SELECT 'TEST' || LPAD('TEST', 10, char(32)) FROM DUMMY; -- Returns 'TEST TEST'