sql_exp - sql_string_func - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Operands and Expressions → Open SQL - Built-In Functions sql_func → Open SQL - SQL Functions →

sql_exp - sql_string_func

Syntax

... func( arg1[, arg2] ... ) ...

Effect

Calls a string function func as an SQL expression or operand of an expression in Open SQL. The arguments arg1, arg2, ... of the function are specified as a comma-separated list in parentheses. A blank must be placed after the opening parenthesis and before the closing parenthesis.

The following table shows the string functions that can be specified as SQL expressions and the requirements made on the arguments. The meaning of the functions can be found under SQL Functions for Strings.

Syntax	Valid Argument Types	Result Type
CONCAT( expr1,expr2 )	See below	SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
CONCAT_WITH_SPACE( expr1,expr2,spaces )	expr1, expr2: see below spaces: Literal or constant host variable with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1331	SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
INSTR( expr,sub )	expr: see below sub: Literal or constant host variable with the ABAP type c, n, d, or t	INT4
LEFT( expr,len )	expr: see below len: Literal or constant host variable with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333	SSTRING if expr has the type SSTRING, else CHAR with the length of the result
LENGTH( expr )	See below	INT4
LOWER( expr )	See below	SSTRING if expr has the type SSTRING, else CHAR with the length of expr.
LPAD( expr,len,src )	expr: see below len: Literal or constant host variable with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333 src: Literal or constant host variable with the ABAP type c, d, t, n, or string with a maximum of 1333 characters	SSTRING if expr has the type SSTRING, else CHAR with the length of len.
LTRIM( expr,char )	expr: see below char: Literal or constant host variable with the ABAP type c or n with the length 1	SSTRING if expr has the type SSTRING, else CHAR with the length of expr.
REPLACE( expr1,expr2,expr3 )	See below	SSTRING if an argument has the type SSTRING, else CHAR with the maximum possible length of the result.
RIGHT( expr,len )	expr: see below len: Literal or constant host variable with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333	SSTRING if expr has the type SSTRING, else CHAR with the length of the result
RPAD( expr,len,src )	expr: see below len: Literal or constant host variable with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333 src: Literal or constant host variable with the ABAP type c, d, t, n, or string with a maximum of 1333 characters	SSTRING if expr has the type SSTRING, else CHAR with the length of len.
RTRIM( expr,char )	expr: see below char: Literal or constant host variable with the ABAP type c or n with the length 1	SSTRING if expr has the type SSTRING, else CHAR with the length of expr.
SUBSTRING( expr,pos,len )	expr: see below pos: Literal, host variable or host expression with the ABAP type b, s, i, or int8	SSTRING if expr has the type SSTRING, else CHAR with the length of len
UPPER( expr )	See below	SSTRING if expr has the type SSTRING, else CHAR with the length of expr.

The arguments expr, expr1, expr2, and expr3 can be SQL expressions, more specifically individual columns, literals, SQL functions, host variables, or host expressions. The possible data types are the dictionary types CHAR, CLNT, CUKY, DATS, LANG, NUMC, TIMS, UNIT, and SSTRING. The possible data types for literals, host variables, and host expressions are the ABAP types assigned to the dictionary types above. The result types are also dictionary types.

If the argument of a string function has the null value, the result of the full string function is the null value.

Note

The argument len of the functions RIGHT and SUBSTRING must only be a real constant in static clauses. In clauses of an Open SQL statement specified in parentheses, the argument can also be a variable evaluated at runtime.

Example

The SELECT statement gets the maximum length of a URL in the database table SCARR.

SELECT FROM scarr
FIELDS MAX( length( url ) ) AS maxlen
INTO @DATA(result).

cl_demo_output=>display( result ).

Example

Concatenates multiple columns of a database table as a character-like column in the program DEMO_SQL_FUNCTION_CONCAT using CONCAT. An alignment is specified using LPAD and RPAD. A concatenation of this type is not possible using the operator &&.

SELECT CONCAT_WITH_SPACE( CONCAT( carrid,
                          LPAD( carrname,21,' ' ) ),
                          RPAD( url,40,' ' ), 3 ) AS line
       FROM scarr
       INTO TABLE @DATA(result).

Executable Example

String functions