AS ABAP Release 758, ©Copyright 2023 SAP SE. All rights reserved.

ABAP - Keyword Documentation →  ABAP - Programming Language →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_func →  ABAP SQL - Built-In Functions sql_func →  sql_func - SQL Functions → 
Mail Feedback

sql_func - String Functions

Syntax

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

Effect

Calls a string function func as an SQL expression or operand of an expression in ABAP 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 in front of the closing parenthesis.

The following table shows the string functions that can be specified as SQL expressions and the requirements on the arguments. The value x in the ABAP SQL Engine column indicates that the function can be executed in the ABAP SQL engine and that the use of this function does not bypass table buffering and does not cause the transport of an internal table accessed with FROM @itab to the database.

Syntax Meaning Valid Argument Types Result Type ABAP SQL Engine
CONCAT( sql_exp1,sql_exp2 ) Concatenates strings in sql_exp1 and sql_exp2. Trailing blanks in sql_exp1, sql_exp2, and in the result are ignored. The maximum length of the result is 1333. See below SSTRING if an argument has the type SSTRING, otherwise CHAR with the length of the result. x
CONCAT_WITH_SPACE( sql_exp1,sql_exp2,spaces ) Concatenates strings in sql_exp1 and sql_exp2 as with CONCAT. The number of blanks specified in spaces is inserted between sql_exp1 and sql_exp2. The maximum length of the result is 1333. sql_exp2: see below

spaces: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333		 SSTRING if an argument has the type SSTRING, otherwise CHAR with the length of the result. x
INITCAP( sql_exp ) String with a length of sql_exp, in which the first letter of a word is transformed to uppercase, and all other letters are transformed to lowercase. A word is delimited by the following: beginning of a string, blank space, new line, form feed, carriage return, line feed, and anything after ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | } ~. See below SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of sql_exp -
INSTR( sql_exp,sub ) Position of the first occurrence of the string from sub in sql_exp (case-sensitive). sql_exp 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. sql_exp: see below

sub: Literal or host constant with the ABAP type c, n, d, or t		 INT4 -
LEFT( sql_exp,len ) String of the length len with the len left characters of sql_exp (ignoring the trailing blanks). The value of len cannot be greater than the length of sql_exp. sql_exp: see below

len: SQL expression with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of the result x
LENGTH( sql_exp ) Number of characters in sql_exp ignoring trailing blanks See below INT4 x
LIKE_REGEXPR( pcre = pcre,
             value = sql_exp[,  
              case_sensitive = case] )		 Checks whether sql_exp contains any occurrence of a Perl Compatible Regular Expression (PCRE) pcre and returns 1 if yes and 0 if no. The search is case-sensitive by default, but this can be overridden using the parameter case. pcre: see below
sql_exp: see below
case: 'X', ' ', or null expression		 INT4 -
LOCATE( sql_exp, sub[,start[,occ]] ) Returns the position of a substring sub in sql_exp (case-sensitive). Both sql_exp and sub respect all blanks and must contain at least one character. start and occ are optional parameters. occ can only be specified if start is specified. start specifies the offset from which to start the search, and occ determines the number of occurrences.

Notes on the result:
If no occurrences are found or occ is less than 1, the result is 0.
If sql_exp, sub or occ are specified as null value, the result is 0.
If start is greater than 0, the matching is carried out starting from this position.
If start is 0, NULL or not specified, the matching is carried out starting from the first position. A setting of 1 for start has the same effect.
If start is less than 0, the starting position is the end of the string, i. e. if start is -2, the starting position is the second to last character of the string. The matching then goes in the reverse direction from right to left.
If occ is specified, the matched position is returned. If no match is found with the specified occurrence, the result is 0.
If occ is not specified, the first matched position is returned. A setting of 1 for occ is the same as not specifying it.		 sql_exp: see below

sub: Literal or host constant with the ABAP type c, n, d, or t, or null expression

occ: i or int8

start: i or int8		 INT4 -
LOCATE_REGEXPR( pcre = pcre,
               value = sql_exp[,
               occurrence = occ][,
               case_sensitive = case][,
               start = start][,
               group = group] )		 Searches sql_exp for a Perl Compatible Regular Expression (PCRE) pattern pcre and returns the offset of the match. The other parameters are optional. occ determines the number of occurrences of pcre. The search is case-sensitive by default, but this can be overridden using the parameter case. The parameter start specifies the offset from which to start the search. The parameter group specifies the number of the group of the matched substring.

If occ is not greater than 0 or start or group are less than 0, the result is a null value.		 pcre: see below

sql_exp: see below

occ: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333; expressions are also possible if they return an integer of type INT4

case: 'X', ' ', or null expression

start: i or int8

group: i or int8		 INT4 -
LOCATE_REGEXPR_AFTER( pcre = pcre,
                     value = sql_exp[,
                      occurrence = occ][,
                     case_sensitive = case][,
                      start = start][,
                     group = group] )		 Searches sql_exp for a Perl Compatible Regular Expression (PCRE) pattern pcre and returns the offset of the match plus 1. The other parameters are optional. occ determines the number of occurrences of pcre. The search is case-sensitive by default, but this can be overridden using the parameter case. The parameter start specifies the offset from which to start the search. The parameter group specifies the number of the group of the matched substring.

If occ is not greater than 0 or start or group are less than 0, the result is a null value.		 pcre: see below

sql_exp: see below

occ: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333; expressions are also possible if they return an integer of type INT4

case: 'X', ' ', or null expression

start: i or int8

group: i or int8		 INT4 -
LOWER( sql_exp ) String with a length of sql_exp, in which all uppercase letters are transformed to lowercase letters. See below SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of sql_exp x
LPAD( sql_exp,len,src ) String of the length len with the right-aligned content of sql_exp 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 sql_exp 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 sql_exp, it is truncated on the right. If src is empty and len is greater than the length. sql_exp: see below

len: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333

src: Literal or host constant with the ABAP type c, d, t, n, or string with a maximum of 1333 characters		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of len. -
LTRIM( sql_exp,char ) String with the content of sql_exp in which all trailing blanks and leading characters are removed that match the character in char. A blank in char is significant. sql_exp: see below

char: Literal or host constant with the ABAP type c or n with the length 1		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of sql_exp -
OCCURRENCES_REGEXPR( pcre = pcre,
                    value = sql_exp[,  
                     case_sensitive = case] )		 Counts all occurrences of a Perl Compatible Regular Expression (PCRE) pcre in sql_exp and returns the number of occurrences. The search is case-sensitive by default, but this can be overridden using the parameter case. pcre: see below
sql_exp: see below
case: 'X', ' ', or null expression		 INT4 -
REPLACE( sql_exp1,sql_exp2,sql_exp3 ) String sql_exp1, in which all instances of sql_exp2 are replaced by the content from sql_exp3. The replacement of letters is case-sensitive. Trailing blanks are ignored in all arguments. The maximum length of the result is 1333. See below SSTRING if an argument has the type SSTRING, otherwise CHAR with the maximum possible length of the result. -
REPLACE_REGEXPR( pcre = pcre,
                value = sql_exp1,
                with = sql_exp2[,
                occurrence = occ][,
                case_sensitive = case][,
                start = start] )		 A Perl Compatible Regular Expression (PCRE) pcre is replaced in sql_exp1 with the character string specified in sql_exp2. 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. The parameter start specifies the offset from which to start the search. pcre: see below

sql_exp1: see below

sql_exp2: see below

occ: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333; expressions are also possible if they return an integer of type INT4

case: 'X', ' ', or null expression

start: i or int8		 SSTRING -
RIGHT( sql_exp,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 sql_exp. sql_exp: see below

len: SQL expression with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of the result x
RPAD( sql_exp,len,src ) String of the length len with the left-aligned content of sql_exp 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 sql_exp 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 sql_exp, it is truncated on the right. If src is empty and len is greater than the length of sql_exp, sql_exp remains unchanged. sql_exp: see below

len: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333

src: Literal or host constant with the ABAP type c, d, t, n, or string with a maximum of 1333 characters		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of len. -
RTRIM( sql_exp,char ) String with the content of sql_exp in which all trailing blanks are removed and all trailing characters that match the character in char. A blank in char is significant. sql_exp: see below

char: Literal or host constant with the ABAP type c or n with the length 1		 SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of sql_exp -
SUBSTRING( sql_exp,pos,len ) Substring sql_exp from the position pos with length len. pos and len must be specified so that the substring is within sql_exp.

The function is always executed in the table buffer.		 sql_exp: see below

pos: SQL expression with the ABAP type b, s, i, int8

len: SQL expression with the ABAP type b, s, i, int8		 If len is a constant:
SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of len
If len is not a constant:
the result has the same data type as the first parameter (sql_exp)		 x
SUBSTRING_REGEXPR( pcre = pcre,
                  value = sql_exp[,
                  occurrence = occ][,
                  case_sensitive = case][,
                  start = start][,
                  group = group] )		 Searches sql_exp for a Perl Compatible Regular Expression (PCRE) pattern pcre and returns the matched substring. The other parameters are optional. occ determines the number of occurrences of pcre. The search is case-sensitive by default, but this can be overridden using the parameter case. The parameter start specifies the offset from which to start the search. The parameter group specifies the number of the group of the matched substring.

If occ is not greater than 0 or start or group are less than 0, the result is a null value.		 pcre: see below

sql_exp: see below

occ: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333; expressions are also possible if they return an integer of type INT4

case: 'X', ' ', or null expression

start: i or int8

group: i or int8		 The same type as sql_exp. -
UPPER( sql_exp ) String with a length of sql_exp, in which all lowercase letters were transformed to uppercase letters. See below SSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of sql_exp x

The arguments sql_exp, sql_exp1, sql_exp2, and sql_exp3 can be any SQL expressions with the appropriate data types. 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.

The argument pcre can be any SQL expression or null expression with an appropriate data type. The possible data types are CHAR and SSTRING. pcre must contain a Perl Compatible Regular Expression (PCRE). The operands passed to the argument pcre are not interpreted in extended mode. The pattern ?x can be used to enable extended mode.

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

Hints

Example

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

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

cl_demo_output=>display( result ).

Example

Concatenation of multiple columns of a DDIC database table to a character-like column in the class CL_DEMO_SQL_FUNCTION_CONCAT using CONCAT. An alignment is achieved 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 @FINAL(result).

Executable Examples



Continue
Example sql_func - String Functions