Skip to Content
Author's profile photo Horst Keller

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

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

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Łukasz Pęgiel
      Łukasz Pęgiel

      ABAP is going in good direction 🙂

      Author's profile photo Christian Drumm
      Christian Drumm

      Hi Horst,

      cool feature. Can already see quite some nice applications e.g. identifying customers in CRM.

      Christian

      Author's profile photo Christian Guenter
      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

       

      We have && for string concatenations in Open SQL already, but with some restrictions.

       

      Forwarded to development …

       

      Author's profile photo Jan Pascal Tschudy
      Jan Pascal Tschudy

      Hello Horst

      Any news on that topic? I went trough the release changes from 7.51 to 7.55 and havent seen a change towards that issue 🙁 Still having to cope with sometimes rather large concat-constructs which are hard to read/understand compared to a &&.

      Thanks and Greetings

      Jan Pascal

      Author's profile photo Kilian Kilger
      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.

       

      Author's profile photo Raymond Giuseppi
      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.

      Author's profile photo Former Member
      Former Member

      Great news Horst!  These functions will be very useful to us, particularly UPPER for case-insensitive searches.