The ABAPVARCHARMODE: Blanks and Empty Strings in ABAP and SQLScript
Veröffentlicht am 21. April 2021 von | ABAP | AMDP | BW/4HANA | SQLScript |
The two SAP programming languages ABAP and SQLScript (aka. HANA SQL) behave differently when it comes to processing strings with blanks. This can lead to errors and confusion. It is therefore important to understand the ABAP VARCHAR mode or ABAPVARCHARMODE.
Strings in ABAP with Text Fields and Text Strings
Unfortunately, there are already the first differences here even within ABAP. I have taken the terms text fields and text strings from the SAP documentation. Here is the direct comparison
Text fields, internal ABAP type c
Definition with
DATA lv_char(10) TYPE c
VALUE 'Brandeis '.
WRITE lv_char && lv_char.In this data type, spaces are always removed at the end. This is already true for the Text field literal which is noted with a "normal" apostrophe.
Text strings, internal ABAP type string
Definition with
DATA lv_string TYPE string
VALUE `Brandeis `.
WRITE lv_string && lv_string.In the case of the string, the blanks are retained. But only if it is Text string literal with the simple backquotes (=slanted inverted commas) in the source text. Unfortunately, most people don't even know how to type these backquotes on a German keyboard. ;-)
The constant SPACE: Just no space character
By the way, the global constant SPACE is a text field of length 1. And in the ABAP debugger we can see that there is exactly one blank in it. But unfortunately it behaves differently. We notice this when we perform a concatenation with this constant SPACE . It is always ignored:
DATA lv_char(10) TYPE c
VALUE 'Brandeis '.
WRITE lv_char && SPACE && lv_char. " Ergibt: BrandeisBrandeisThe behavior of SPACE is also somewhat confusing because it behaves differently again as a separator in the CONCATENATE statement:
DATA lv_char(10) TYPE c
VALUE 'Brandeis'.
DATA lv_concat(20) TYPE c.
CONCATENATE lv_char lv_char INTO lv_concat SEPARATED BY space.
WRITE lv_concat. "Ergibt: Brandeis BrandeisHere exactly the expected space is inserted!
Strings with spaces in SQLScript
In the SAP HANA database, things are much simpler by default: In SQLScript, strings are really stored and processed exactly as they are. Spaces remain at the end and nothing is automatically removed. But of course there is one exception that is almost the rule: the ABAP VARCHAR mode ((See SAP Note 2262114 - One Space Handling for ABAP in SAP HANA)). This is used when the SAP HANA database is located under an ABAP system such as an S/4HANA or BW/4HANA in order to be compatible with the SPACE of the ABAP.
Why an extra mode?
Empty or initial character strings are sent to the database by ABAP as SPACE, which is actually a blank character. In ABAP, SPACE therefore also works correctly as a comparison value in WHERE conditions. This means that a selection in SQLScript should actually take place with a space character. Use the ABAP VARCHAR mode to communicate the HANA: Make no distinction between a single blank literal and an empty string. Both are mapped to the empty string. This makes the selection in SQLScript easier.
This solves the selection problem, but a new problem has arisen: If we ever need a single space character just like that, we can no longer specify it as a literal ' '. Because it automatically becomes a ''. So, as with text fields in ABAP, we have a problem if we want to separate two words with a space, for example. Instead of a literal with a space, we instead need to trouble the function call CHAR(32) to produce us a space:
SELECT 'Peter' || ' ' || 'Mueller' FROM DUMMY; --Not working as expected
SELECT 'Peter' || char(32) || 'Mueller' FROM DUMMY; --Separated with a single blankThe session variable ABAPVARCHARMODE
We can find out directly in our source code whether the ABAP VARCHAR mode is active or not. This information is in the session variable ABAPVARCHARMODE. If this is set to TRUE, the processing in SQLScript behaves as described above. We can also set this mode ourselves for testing purposes. However, this is dangerous. This is because, as a session parameter, this setting applies to the entire database session. Here we should not change the default value for AMDPs.
But to try it out in the SQL console, you can use this code here. It nicely shows how the behavior changes when queried:
SET 'ABAPVARCHARMODE' = 'FALSE';
SELECT 'Peter' || ' ' || 'Mueller' FROM DUMMY;
SET 'ABAPVARCHARMODE' = 'TRUE';
SELECT 'Peter' || ' ' || 'Mueller' FROM DUMMY;Summary
Spaces at the end of a string are normally removed in ABAP, but not in SQLScript. Empty strings or strings and literals that consist of exactly one blank character cause problems in SQLScript if an ABAP system is involved. This is because there is a mapping from a space character to the empty string. However, the function call CHAR(32) is a good way to get around this.
