ABAP News for Release 7.51 – Case Insensitive Search in SQL and Other New Functions

Some time ago someone asked for upper and case insensitive searches in Open SQL and master Kilger answered  “Emulating ABAP’s UCS2 semantics on Any-DB isn’t exactly like celebrating a party 🙂.” Nevertheless, he’s done it!

With ABAP 7.51 you can write something like:

DATA(query) = `...`.
DATA(rows)  = ...

query =  `%` && to_upper( query ) && `%`.
SELECT arbgb, msgnr, text
       FROM t100
       WHERE sprsl = 'E' AND
             upper( text ) LIKE @query
       ORDER BY arbgb, msgnr, text
       INTO TABLE @DATA(result)
       UP TO @rows ROWS.

The new built-in function upper in combination with LIKE enables a case insensitive search in Open SQL. But upper is not only available in Open SQL but in ABAP CDS too. With ABAP 7.51 you can define a CDS view as follows:

@AbapCatalog.sqlViewName: 'DEMOCDSUPPER' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view Demo_Cds_Upper 
  as select from 
    t100 
    { 
      sprsl, 
      arbgb, 
      msgnr, 
      text, 
      upper(text) as upper_text 
    } 

You cannot use a SQL function as LHS of LIKE in ABAP CDS yet and therefore, a helper column upper_text is defined. The following Open SQL SELECT accesses the above view giving the same result as the foregoing one.

    SELECT arbgb, msgnr, text
           FROM demo_cds_upper
           WHERE sprsl = 'E' AND
                 upper_text LIKE @query
           ORDER BY arbgb, msgnr, text
           INTO TABLE @DATA(result)
           UP TO @rows ROWS.

All in all, there are the following new SQL functions available as built-in functions in Open SQL and ABAP CDS with ABAP 7.51:

• Open SQL: DIVISION, LOWER, UPPER, LEFT, CONCAT_WITH_SPACE, INSTR, RPAD, DATS_IS_VALID, DATS_DAYS_BETWEEN, DATS_ADD_DAYS, and DATS_ADD_MONTHS
• ABAP CDS: LOWER, UPPER, ABAP_SYSTEM_TIMEZONE, ABAP_USER_TIMEZONE, TSTMP_TO_DATS, TSTMP_TO_TIMS, TSTMP_TO_DST, DATS_TIMS_TO_TSTMP, and FLTP_TO_DEC

The following tables summarize the SQL functions that are available in ABAP 7.51 for Open SQL and ABAP CDS.

Numeric Functions

String Functions

Binary Functions

Coalesce

Conversion Functions

Date and Time Functions

For more information see Built-In Functions in ABAP Dictionary