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
