【11】SAP ABAP性能优化 - SQL中CONCAT保留字符串拼接中的空格

1. CONCAT的问题

SAP的OPEN SQL中提供了丰富的字符串操作函数，其中较为常用的是CONCAT关键字，通过CONCAT可以实现两个字段字符串的拼接，但在拼接过程中，字符串间的空格会被“吃掉”，将拼接的结果组成一个完整连续的字符。

但很多情况下，我们是希望保留字段中的空格的，那么如何实现呢？

结合上篇博客【10】SAP ABAP性能优化 - 在SQL语句中操作String查询复合主键AWKEY中介绍的SQL中CONCA的用法，本文中将进一步介绍保留“空格”的方式。

2. 双重REPLACE实现空格的保留

为了保留空格，我们使用双重replace的方式，其核心代码如下：

示例代码：

TYPES: BEGIN OF ts_doc_key,
         bukrs TYPE bkpf-bukrs,
         belnr TYPE bkpf-belnr,
         gjahr TYPE bkpf-gjahr,
       END OF ts_doc_key,
       BEGIN OF ts_doc_header,
         bukrs TYPE bkpf-bukrs,
         belnr TYPE bkpf-belnr,
         gjahr TYPE bkpf-gjahr,
         awtyp TYPE bkpf-awtyp,
         awsys TYPE bkpf-awsys,
         awkey TYPE bkpf-awkey,
       END OF ts_doc_header.

DATA: lt_doc_key TYPE SORTED TABLE OF ts_doc_key WITH UNIQUE KEY bukrs belnr gjahr.
DATA: lt_header1 TYPE STANDARD TABLE OF ts_doc_header WITH EMPTY KEY,
      lt_header2 TYPE STANDARD TABLE OF ts_doc_header WITH EMPTY KEY,
      lt_header3 TYPE STANDARD TABLE OF ts_doc_header WITH EMPTY KEY
      .

BREAK-POINT.

**********************************************************************
*  CONCAT - Keep Blank 
**********************************************************************
* prepare 10 document keys
SELECT bukrs,
       belnr,
       gjahr
  FROM bkpf
  WHERE ( awtyp = 'BKPF' OR awtyp = 'BKPFF') AND ( bukrs = '88' OR bukrs = 'AAA') AND awkey IS NOT INITIAL
   INTO TABLE @lt_doc_key   UP TO 10 ROWS.

* different result
SELECT  a~bukrs AS bukrs,
        a~belnr AS belnr,
        a~gjahr AS gjahr,
        a~awtyp AS awtyp,
        a~awsys AS awsys,
        a~awkey AS awkey,
        concat( concat( b~belnr , b~bukrs ), b~gjahr ) AS wrong_awkey,
        concat( b~belnr, replace( replace( concat( rpad( b~bukrs, 4, '*' ), b~gjahr  ),
                                          '*', ' + ' ),
                                 '+', ' ' ) )
               AS right_awkey
FROM bkpf AS a INNER JOIN @lt_doc_key AS b
  ON a~bukrs = b~bukrs AND a~belnr = b~belnr
WHERE a~awtyp = 'BKPF' OR a~awtyp = 'BKPFF'
INTO TABLE @DATA(lt_result).

* concat ( BUKRS, BELNR, GJAHR ) to join the composite key BKPF-AWKEY.
SELECT a~bukrs,
       a~belnr,
       a~gjahr,
       a~awtyp,
       a~awsys,
       a~awkey
  FROM bkpf AS a INNER JOIN @lt_doc_key AS b
    ON a~awkey = concat( b~belnr, replace( replace( concat( rpad( b~bukrs, 4, '*' ), b~gjahr  ),
                                                    '*', ' + ' ),
                                           '+', ' ' ) )
 WHERE a~awtyp = 'BKPF' OR a~awtyp = 'BKPFF'
  INTO TABLE @lt_header1.

BREAK-POINT.

可以看到单纯CONCAT, 以及在拼接字符串时使用双重REPLACE后，实现保留空格的对比效果。

3. 字符串操作函数汇总

SAP在OPEN SQL中提供了丰富的字符串操作函数，对于遇到的具体问题，可以参照下面的程序寻找合适的解决方案。

REPORT demo_sql_function_string.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    DELETE FROM demo_expressions.
    INSERT demo_expressions FROM TABLE @( VALUE #(
      ( id = 'X'
        char1 = ' 0123'
        char2 = 'aAaA' ) ) ).

    SELECT SINGLE
           char1 AS text1,
           char2 AS text2,
           CONCAT(            char1,char2 )     AS concat,
           CONCAT_WITH_SPACE( char1,char2, 1 )  AS concat_with_space,
           INSTR(             char1,'12' )      AS instr,
           LEFT(              char1,3 )         AS left,
           LENGTH(            char1 )           AS length,
           LOWER(             char2 )           AS lower,
           LPAD(              char1,10,'x' )    AS lpad,
           LTRIM(             char1,' ' )       AS ltrim,
           REPLACE(           char1,'12','__' ) AS replace,
           RIGHT(             char1,3 )         as right,
           RPAD(              char1,10,'x' )    AS rpad,
           RTRIM(             char1,'3' )       AS rtrim,
           SUBSTRING(         char1,3,3 )       AS substring,
           UPPER(             char2 )           AS upper
           FROM demo_expressions
           INTO @DATA(result).

    cl_demo_output=>display( result ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).