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

SQL Function Result ABAP CDS Open SQL
ABS(arg) Absolute amount of arg. x x
CEIL(arg) Smallest integer number not less than the value of arg. x x
DIV(arg1, arg2) Integer part of the division of arg1 by arg2 The sign is assigned after the amounts are divided; positive if the arguments have the same sign, and negative if the arguments have different signs. Exception: arg2 has the value 0. x x
DIVISION(arg1, arg2, dec) Division of arg1 by arg2 The result is rounded to dec decimal places. x x
FLOOR(arg) Largest integer number not greater than the value of arg. x x
MOD(arg1, arg2) Integer remainder of the division of arg1 by arg2. x x
ROUND(arg, pos) Rounded value of arg. If pos is greater than 0, the value is rounded to the position pos on the right of the decimal separator. If this is not the case, position abs(pos)+1 to the left of the decimal separator is rounded. This results in a 0 if the number of places is not sufficient. x x

String Functions

SQL Function Result ABAP CDS Open SQL
CONCAT( arg1, arg2 ) Chaining of character strings in arg1 and arg2. Trailing blanks in arg1, arg2, and in the result are ignored. The maximum length of the result is 1333. x x
CONCAT_WITH_SPACE( arg1, arg2, spaces ) Concatenation of strings in arg1 and arg2 as with CONCAT. The number of blanks specified in spaces is inserted between arg1 and arg2. The maximum length of the result is 1333. x x
INSTR( arg, sub ) Position of the first occurrence of the string from sub in arg (case-sensitive). arg respects leading blanks and ignores trailing blanks. sub respects all blanks. sub must contain at least one character. If no occurrences are found, the result is 0. x x
LEFT( arg, len ) String of the length len with the len left characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg. x x
LENGTH( arg ) Number of characters in arg ignoring trailing blanks. x x
LOWER( arg ) String with the content of arg in lower case. x x
LPAD( arg, len, src ) String of the length len with the right-justified content of arg without trailing blanks and in which leading blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged. x x
LTRIM( arg, char ) String with the content of arg in which all trailing blanks are removed and all leading characters that match the character in char. A blank in char is significant. x x
REPLACE( arg1, arg2, arg3 ) Character string arg1, in which all instances of arg2 are replaced by the content from arg3. The replacement of letters is case-sensitive. Trailing blanks are ignored in all arguments. The maximum length of the result is 1333. x x
RIGHT( arg, len ) String of the length len with the len right characters of arg (ignoring the trailing blanks). The value of len cannot be greater than the length of arg. x x
RPAD( arg, len, src ) String of the length len with the left-justified content of arg without trailing blanks and in which trailing blanks produced by the expanded string are replaced by the characters from the argument src (respecting all blanks). Trailing blanks from arg are preserved. If more characters are required than exist in src, the content of src is used repeatedly. If len is less than the length of arg, it is truncated on the right. If src is empty and len is greater than the length of arg, arg remains unchanged. x x
RTRIM( arg, char ) String with the content of arg in which all trailing blanks are removed and all trailing characters that match the character in char. A blank in char is significant. x x
SUBSTRING( arg, pos, len ) Substring of arg from the position pos in the length len. pos and len must be specified so that the substring is within in arg. x x x x
UPPER( arg ) String with the content of arg in upper case. x x

Binary Functions

SQL Function Result ABAP CDS Open SQL
BINTOHEX(arg) Character string containing the half bytes arg converted to the hexadecimal characters “0” to “9” and “A” to “F” (left-justified). x
HEXTOBIN(arg) Byte string whose half bytes are determined from the hexadecimal characters in arg. Any leading blanks are removed before the conversion from arg and all trailing blanks are then replaced by “0”. x

 

Coalesce

SQL Function Result ABAP CDS Open SQL
COALESCE( arg1, arg2, … ) Value of the first argument that is not null. x x

Conversion Functions

SQL Function Result ABAP CDS Open SQL
FLTP_TO_DEC( arg AS dtype ) Converts an argument of type FLTP into a packed number. x
UNIT_CONVERSION( p1 => a1, p2 => a2, … ) Unit conversions. x
CURRENCY_CONVERSION( p1 => a1, p2 => a2, … ) Currency conversions. x
DECIMAL_SHIFT( p1 => a1, p2 => a2, … ) Sets the decimal point. x

Date and Time Functions

SQL function Result ABAP CDS Open SQL
DATS_IS_VALID( date ) Shows if the argument is a valid date. x x
DATS_DAYS_BETWEEN( date1, date2 ) Difference of two dates. x x
DATS_ADD_DAYS( date, days , on_error ) Sum of days and a date. x x
DATS_ADD_MONTHS( date, months, on_error ) Sum of months and a date. x x
TIMS_IS_VALID( time ) Shows if the argument is a valid time. x
TSTMP_IS_VALID( tstmp ) Shows if the argument is a valid timestamp. x
TSTMP_CURRENT_UTCTIMESTAMP( ) Current UTC time stamp. x
TSTMP_SECONDS_BETWEEN( tstmp1, tstmp2, on_error ) Difference of two timestamps in seconds. x
TSTMP_ADD_SECONDS( tstmp, seconds, on_error ) Sum of seconds and a timestamp. x
TSTMP_TO_DATS( tstmp, tzone, clnt, on_error ) Local date of a timestamp. x
TSTMP_TO_TIMS( tstmp, tzone, clnt, on_error ) Local time of a timestamp. x
TSTMP_TO_DST( tstmp, tzone, clnt, on_error ) Locale summer time flag of a timestamp. x
DATS_TIMS_TO_TSTMP( date, time, tzone, clnt, on_error ) Timestamp for a local date and time. x
ABAP_SYSTEM_TIMEZONE( clnt, on_error ) System time zone of the AS ABAP. x
ABAP_USER_TIMEZONE( user, clnt, on_error ) User time zone of the AS ABAP. x

 

 

For more information see Built-In Functions in ABAP Dictionary

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Christian Guenter

    Hi Horst,

    nice stuff as always.

    Wouldn’t it be great if CONCAT (and CONCAT_WITH_SPACE) were variadic functions?

    E.g. we could write

    SELECT FROM pa0002
           FIELDS CONCAT( pernr, vorna, nachn, begda ) AS info
           INTO TABLE @DATA(table).
    

    instead of

    SELECT FROM pa0002
           FIELDS CONCAT( CONCAT( pernr, CONCAT( vorna, nachn ) ), begda ) AS info
           INTO TABLE @DATA(table).
    

    Btw. that’s what you can do in SQL-92 standard

    SELECT CustomerName 
        || ContactName 
        || Address 
    FROM Customers;

    in which || is the concatenation operator.

    Regards Christian

    (4) 
    1. Kilian Kilger

      Hi,

      it is on my list. Actually the coding change to support this in the ABAP kernel would be 3 lines. But somebody has to write the tests. And one needs a bit more than 3 tests :-).

      Best regards,
      Kilian.

       

      (7) 
  2. Raymond Giuseppi

    Well some hundredths of threads get answered (search archive with search help and upper/lower case or similar keywords)

    really nice stuff, keep up the good work.

    (2) 

Leave a Reply