Kontakt

Ansicht
Dokumentation

ABENCDS_SQL_FUNCTIONS_CHARACTER_V2 - CDS SQL FUNCTIONS CHARACTER V2

ABENCDS_SQL_FUNCTIONS_CHARACTER_V2 - CDS SQL FUNCTIONS CHARACTER V2

BAL_S_LOG - Application Log: Log header data   Vendor Master (General Section)  
This documentation is copyright by SAP AG.
SAP E-Book

- CDS View Entity, String Functions

The following table shows the possible SQL functions for character strings in a CDS view entity, plus the requirements made on the arguments.

Function Result Valid Argument Types Result Type
CONCAT(arg1, arg2) Concatenates strings in arg1 and arg2. Trailing blanks in arg1, arg2, and in the result are ignored. The maximum length of the result is 1333. arg1, arg2: see below SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
CONCAT_WITH_SPACE(arg1, arg2, spaces) Concatenates 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. arg1, arg2: see below \lbr\lbr spaces: positive numeric literal greater than 0. The upper limit of spaces is 1333 minus arg1 and arg2. SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
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. arg: see below \lbr\lbr sub: non-empty character literal INT4
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. arg: see below \lbr\lbr len: \lbr - positive literals not equal to zero\lbr - suitable fields of a data source of the current CDS view entity \lbr - path expressions that identify a suitable field of a data source \lbr - input parameters from the parameter list \lbr - reuse expressions using $projection \lbr - aggregate expressions \lbr - arithmetic expressions \lbr - case distinctions \lbr - cast expressions \lbr - built-in functions. \lbr The operands must have one of the following data types: INT1, INT2, INT4. SSTRING if arg has the type SSTRING, else CHAR with length len
LENGTH(arg) Number of characters in arg ignoring trailing blanks arg: see below INT4
LOWER(arg) String with a length of arg, in which all uppercase letters are transformed to lowercase letters arg: see below, with the exception of NUMC, DATS, and TIMS Data type of arg in the length of arg
LPAD(arg, len, src) String of the length len with the right-aligned 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). Leading 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. arg: see below \lbr\lbr len: positive numeric literal greater than 0 and less than or equal to 1333 \lbr\lbr src: Character literal SSTRING if arg has the type SSTRING, else CHAR with length len
LTRIM(arg, char) String with the content of arg in which all trailing blanks and leading characters are removed that match the character in char. A blank in char is significant. arg: see below \lbr\lbr char: Character literal with length 1 SSTRING if arg has the type SSTRING, else CHAR with the length of arg.
REPLACE(arg1, arg2, arg3) 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. arg1, arg2, arg3: see below SSTRING if arg1 or arg3 has the type SSTRING, else CHAR with the maximum possible length of the result.
REPLACE_REGEXPR(PCRE => pcre, \lbr                VALUE => arg1, \lbr                WITH => arg2, \lbr                RESULT_LENGTH => res[, \lbr                OCCURRENCE => occ][, \lbr                CASE_SENSITIVE => case][, \lbr                SINGLE_LINE => bool][, \lbr                MULTI_LINE => bool][, \lbr                UNGREEDY => bool]) A Perl Compatible Regular Expression (PCRE) pcre is replaced in arg1 with the character string specified in arg2. occ is optional and determines the number of occurrences of pcre to be replaced. By default, all occurrences are replaced. The search is case-sensitive by default, but this can be overridden using the parameter case. Single-line, multiline and ungreedy regular expression matching can be set with the parameter bool. pcre: Perl Compatible Regular Expression (PCRE) \lbr \lbr arg1: see below, with the exception of CLNT \lbr\lbr arg2: see below, with the exception of CLNT \lbr\lbr res: positive numeric literal greater than 0 and less than or equal to 1333 \lbr\lbr occ: Positive numeric literal of type INT1, INT2, or INT4 greater than or equal to 1. Alternatively, ALL can be specified. In this case, all occurrences of the value arg1 are replaced. \lbr\lbr case: 'X' or ' '. Alternatively, the character literals 'true' or 'false' (case-sensitive), that are internally handled like the values 'X' or ' ', can be used. The default value is 'true'. \lbr\lbr bool: 'X' or ' '. Alternatively, the character literals 'true' or 'false' (case-sensitive), that are internally handled like the values 'X' or ' ', can be used. The default value is 'false'. SSTRING with the maximum possible length of res.
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. arg: see below \lbr\lbr len: \lbr - positive literals not equal to zero\lbr - suitable fields of a data source of the current CDS view entity \lbr - path expressions that identify a suitable field of a data source \lbr - input parameters from the parameter list \lbr - reuse expressions using $projection \lbr - aggregate expressions \lbr - arithmetic expressions \lbr - case distinctions \lbr - cast expressions \lbr - built-in functions. \lbr The operands must have one of the following data types: INT1, INT2, INT4. SSTRING if arg has the type SSTRING, else CHAR with length len
RPAD(arg, len, src) String of the length len with the left-aligned 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). Leading 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. arg: see below \lbr\lbr len: positive numeric literal greater than 0 and less than or equal to 1333 \lbr\lbr src: Character literal SSTRING if arg has the type SSTRING, else CHAR with length len
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. arg: see below \lbr\lbr char: Character literal with length 1 SSTRING if arg has the type SSTRING, else CHAR with the length of arg.
SUBSTRING(arg, pos, len) Substring arg from the position pos with length len. pos and len must be specified so that the substring is within arg. arg: see below \lbr\lbr pos and len: \lbr - positive literals not equal to zero\lbr - suitable fields of a data source of the current CDS view entity \lbr - path expressions that identify a suitable field of a data source \lbr - input parameters from the parameter list \lbr - reuse expressions using $projection \lbr - aggregate expressions \lbr - arithmetic expressions \lbr - case distinctions \lbr - cast expressions \lbr - built-in functions. \lbr The operands must have one of the following data types: INT1, INT2, INT4. SSTRING if arg has the type SSTRING, else CHAR or NUMC with length of at lest len
UPPER(arg) String with a length of arg, in which all lowercase letters are transformed to uppercase letters. See below, with the exception of NUMC, DATS, and TIMS Data type of arg in the length of arg

The following can be specified as the arguments arg:

  • The following built-in functions and expressions (if they return a matching type):

  • Type modifications using CAST

The valid argument types for arg, arg1, arg2, and arg3 are CHAR, CLNT, LANG, NUMC, CUKY, UNIT, DATS, TIMS, and SSTRING.

In functions where an explicit length len is specified, the actual length of the result is defined when the CDS view entity is activated and is at least as long as len.

In all functions with the exception of LPAD and RPAD, the trailing blanks of all arguments are removed before the actual processing and the trailing blanks of the result are removed before the return operation. In LPAD and RPAD, the trailing blanks of the argument src are preserved. In the case of UPPER and LOWER, the handling of trailing blanks makes no difference, as the length of the argument stays the same.

Notes

  • The characters in the surrogate area of the system code page UTF-16 are handled as two characters by the CDS string functions. This must be respected when the length is determined and these characters must not be split by mistake. Functions UPPER and LOWER do not change the character of the surrogate area.
  • In the case of the function REPLACE, it should be noted that the maximum possible length of the result can be slightly greater than the allowed length of 1333. This produces a syntax error. In general, the maximum possible length is calculated by dividing the length of arg1 by the length of arg2, multiplied by the length of arg3.
  • The function REPLACE_REGEXPR accesses the PCRE1 library implemented in the SAP HANA database.
  • In CDS view entities, the function REPLACE_REGEXPR allows more parameters than the same function in ABAP SQL (UNGREEDY, for example). However, this functionality can also be implemented through the Perl Compatible Regular Expression syntax itself.

Example

The following CDS view entity applies built-in SQL functions for strings in the SELECT list to columns of the DDIC database table DEMO_EXPRESSIONS. The program DEMO_CDS_SQL_FUNC_STRING_VE uses SELECT to access the view.






RFUMSV00 - Advance Return for Tax on Sales/Purchases   CL_GUI_FRONTEND_SERVICES - Frontend Services  
This documentation is copyright by SAP AG.

Length: 34294 Date: 20240426 Time: 030747     sap01-206 ( 657 ms )