How to add trailing spaces when concatenating two columns?

Question

I am trying to find a way to preserve a space within SQL concatenation.

For context: A table I am selecting from a table with a single concatenated key column. Concatenated keys respect spaces. Example: BUKRS(4) = 'XYZ ', WERKS(4) = 'ABCD' is represented as key XYZ ABCD.

I am trying to form the same value in SQL, but it seems like ABAP SQL auto-trims all trailing spaces.

Select concat( rpad( tvko~bukrs, 4, (' ') ), t001w~werks ) as key, datab, datbi 
    from t001w 
       inner join tvko on tvko~vkorg  = t001w~vkorg
       left  join ztab on ztab~key = concat( rpad( tvko~bukrs, 4, (' ') ), t001w~werks ) "This is why I need the concat

    

• rpad( tvko~bukrs, 4, ' ' ) in this example returns XYZ, instead of XYZ , which leads to concatenated value being XYZABCD, rather than XYZ ABCD.
• lpad seems to work just fine (returning XYZ), which leads me to believe I'm doing something wrong.
• SQL functions don't accept string literals or variables (which preserve spaces in the same circumstances in ABAP) as they are non-elementary types.

Is there any way to pad/preserve the spaces in ABAP SQL (without pulling data and doing it in application server)?

Update: I solved my problem by splitting key selection from data selection and building the key in ABAP AS. It's a workaround that avoids the problem instead of solving it, so I'll keep the question open in case an actual solution appears.

Answer 1

EDIT: this post doesn't answer the question of inserting a number of characters which vary based on values in some table columns e.g. LENGTH function is forbidden in RPAD( tvko~bukrs, LENGTH( ... ), (' ') ). It's only starting from ABAP 7.55 that you can indicate SQL expressions instead of fixed numbers. You can't do it in ABAP before that. Possible workarounds are to mix ABAP SQL and ABAP (e.g. LIKE 'part1%part2' and then filtering out using ABAP) or to use native SQL directly (ADBC, AMDP, etc.)

Concerning how the trailing spaces are managed in OpenSQL/ABAP SQL, they seem to be ignored, the same way as they are ignored with ABAP fixed-length character variables.

Demonstration: I simplified your example to extract the line Walldorf plant:

These ones don't work (no line returned):

SELECT * FROM t001w
    WHERE concat( 'Walldorf ' , 'plant' ) = t001w~name1
    INTO TABLE @DATA(itab_1).

SELECT * FROM t001w
    WHERE concat( rpad( 'Walldorf', 1, ' ' ), 'plant' ) = t001w~name1
    INTO TABLE @DATA(itab_2).

These 2 ones work, one with leading space(s), one using concat_with_space:

SELECT * FROM t001w
    WHERE concat( 'Walldorf',  ' plant' ) = t001w~name1
    INTO TABLE @DATA(itab_3).

SELECT * FROM t001w
    WHERE concat_with_space( 'Walldorf', 'plant', 1 ) = t001w~name1
    INTO TABLE @DATA(itab_4).

General information: ABAP documentation - SQL string functions

EDIT: working example added, using leading space(s).