ABAP Keyword Documentation →  ABAP - Dictionary →  ABAP CDS in ABAP Dictionary →  ABAP CDS - Data Definitions →  ABAP CDS - DDL for Data Definitions →  ABAP CDS - DEFINE VIEW →  ABAP CDS - SELECT →  ABAP CDS - SELECT, Built-In Functions →  ABAP CDS - sql_functions 

ABAP CDS - String Functions

The following table shows the possible SQL functions for character strings in a CDS view, plus the requirements made on the arguments. The meaning of the functions can be found under SQL Functions for Strings.

Function	Valid Argument Types	Result Type
CONCAT(arg1, arg2)	See below	SSTRING if an argument has the typeSSTRING, else CHAR with the length of the result.
CONCAT_WITH_SPACE(arg1, arg2, spaces )	arg1, arg2: see below
spaces: positivenumeric literal greater than 0 and less than or equal to 1331	SSTRING if an argument has the typeSSTRING, else CHAR with the length of the result.
INSTR(arg, sub)	arg: see below
sub: non-empty character literal	INT4
LEFT(arg, len)	arg: see below
len: positive numeric literal greater than 0 and less than or equal to 1333	SSTRING if arg has the typeSSTRING, else CHAR with length len
LENGTH(arg)	See below	INT4
LOWER( arg )	See below, with the exception of NUMC,DATS, and TIMS	Data type of arg in the length of arg
LPAD(arg, len, src)	arg: see below
len: positivenumeric literal greater than 0 and less than or equal to 1333
src:Character literal	SSTRING if arg has the typeSSTRING, else CHAR with length len
LTRIM(arg, char)	arg: see below
char: Character literal with length 1	SSTRING if arg has the typeSSTRING, else CHAR with the length of arg.
REPLACE(arg1, arg2, arg3)	See below	SSTRING if arg1 or arg3 has the typeSSTRING, else CHAR with the maximum possible length of the result.
RIGHT(arg,len)	arg: see below
len: positive numeric literal greater than 0 and less than or equal to 1333	SSTRING if arg has the typeSSTRING, else CHAR with length len
RPAD(arg, len, src)	arg: see below
len: positivenumeric literal greater than 0 and less than or equal to 1333
src:Character literal	SSTRING if arg has the typeSSTRING, else CHAR with length len
RTRIM(arg, char)	arg: see below
char: Character literal with length 1	SSTRING if arg has the typeSSTRING, else CHAR with the length of arg.
SUBSTRING(arg, pos, len)	arg: see below
pos and len: positivenumeric literals not equal to zero	SSTRING if arg has the typeSSTRING, elseCHAR or NUMCwith length of at lest len
UPPER( arg )	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:

• Literals of a suitable type. The literal can be prefixed with the name of a domain.

• Suitable fields of a data source data_source of the current CDS view.

• Path expressions that identify a suitable field of a data source data_source.

• Input parameters from the parameter list parameter_list.

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

• Other built-in SQL functions

• Arithmetic expressions

• 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 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 is retained.

Other versions: 7.31 | 7.40 | 7.54

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 permitted 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.

Example

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

@AbapCatalog.sqlViewName: 'DEMO_CDS_STRFUNC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_sql_functions_string
  as select from
    demo_expressions
    {
      length(            char1               ) as r_length,
      instr(             char1, 'CD'         ) as r_instr,
      concat(            char1, char2        ) as r_concat,
      concat_with_space( char1, char2, 10    ) as r_concat_with_space,
      left(              char1, 3            ) as r_left,
      lower(             char1               ) as r_lower,
      right(             char2, 3            ) as r_right,
      lpad(              char1, 10, 'x'      ) as r_lpad,
      rpad(              char2, 10, 'y'      ) as r_rpad,
      ltrim(             char1, 'A'          ) as r_ltrim,
      rtrim(             char1, 'E'          ) as r_rtrim,
      replace(           char2, 'GHI', 'XXX' ) as r_replace,
      substring(         char2, 2, 3         ) as r_substring,
      upper(             char2               ) as r_upper
    }