Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
horst_keller
Product and Topic Expert
Product and Topic Expert
26,989
Some time ago someone asked for upper and case insensitive searches in Open SQL and kilian.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
9 Comments
Labels in this area