Ansicht
Dokumentation

ABENSQL_STRING_FUNC - SQL STRING FUNC

ABENSQL_STRING_FUNC - SQL STRING FUNC

General Material Data   Vendor Master (General Section)  
This documentation is copyright by SAP AG.
SAP E-Book

- String Functions

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

Effect

Calls a string function func as an SQL expression or operand of an expression in . 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 Table Buffer column indicates that the function can be executed in the table buffer and that the use of this function does not bypass table buffering.

Syntax Meaning Valid Argument Types Result Type Table Buffer
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 \lbr\lbr 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 \lbr\lbr 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 \lbr\lbr 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, \lbr              value = sql_exp$[,   \lbr              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: Perl Compatible Regular Expression (PCRE) \lbr sql_exp: see below \lbr case: 'X' or ' ' 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 occurrence. \lbr \lbr Notes on the result: \lbr If no occurrences are found or occ is less than 1, the result is 0. \lbr If sql_exp, sub or occ are specified as null value, the result is 0. \lbr If start is greater than 0, the matching is carried out starting from this position. \lbr 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. \lbr If start is less than 0, the starting position is considered from 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. \lbr If occ is specified, the matched position is returned. If no match is found with the specified occurrence, the result is 0. \lbr 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 \lbr \lbr sub: Literal or host constant with the ABAP type c, n, d, or t \lbr \lbrocc: i or int8 \lbr \lbr start: i or int8 INT4 -
LOCATE_REGEXPR( pcre = pcre, \lbr                value = sql_exp$[, \lbr                occurrence = occ$]$[, \lbr                case_sensitive = case$]$[, \lbr                start = start$]$[, \lbr                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. \lbr \lbr If occ is not greater than 0 or start or group are less than 0, the result is a null value. pcre: Perl Compatible Regular Expression (PCRE) \lbr \lbr sql_exp: see below \lbr \lbrocc: 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 \lbr \lbr case: 'X' or ' ' \lbr \lbr start: i or int8 \lbr \lbr group: i or int8 INT4 -
LOCATE_REGEXPR_AFTER( pcre = pcre, \lbr                      value = sql_exp$[, \lbr                      occurrence = occ$]$[, \lbr                      case_sensitive = case$]$[, \lbr                      start = start$]$[, \lbr                      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. \lbr \lbr If occ is not greater than 0 or start or group are less than 0, the result is a null value. pcre: Perl Compatible Regular Expression (PCRE) \lbr \lbr sql_exp: see below \lbr \lbrocc: 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 \lbr \lbr case: 'X' or ' ' \lbr \lbr start: i or int8 \lbr \lbr 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 \lbr\lbr len: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333 \lbr\lbr 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 \lbr\lbr 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, \lbr                     value = sql_exp$[,   \lbr                     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: Perl Compatible Regular Expression (PCRE) \lbr sql_exp: see below \lbr case: 'X' or ' ' 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, \lbr                 value = sql_exp1, \lbr                 with = sql_exp2$[,\lbr                 occurrence = occ$]$[,\lbr                 case_sensitive = case$]$[,\lbr                 start = start$]$[, \lbr                 group = group$] ) 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. The parameter group specifies the number of the group of the matched substring. pcre: Perl Compatible Regular Expression (PCRE) \lbr \lbr sql_exp1: see below \lbr \lbrsql_exp2: see below \lbr \lbrocc: 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 \lbr \lbr case: 'X' or ' ' \lbr \lbr start: i or int8 \lbr \lbr group: 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 \lbr\lbr 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 \lbr\lbr len: Literal or host constant with the ABAP type b, s, i, or int8 greater than 0 and less than or equal to 1333 \lbr\lbr 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 \lbr\lbr 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. \lbr \lbr The function is always executed in the table buffer. sql_exp: see below \lbr\lbr pos: SQL expression with the ABAP type b, s, i, int8 \lbr\lbr len: SQL expression with the ABAP type b, s, i, int8 If len is a constant: \lbrSSTRING if sql_exp has the type SSTRING, otherwise CHAR with the length of len \lbr If len is not a constant: \lbr the result has the same data type as the first parameter (sql_exp) x
SUBSTRING_REGEXPR( pcre = pcre, \lbr                   value = sql_exp$[, \lbr                   occurrence = occ$]$[, \lbr                   case_sensitive = case$]$[, \lbr                   start = start$]$[, \lbr                   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. \lbr \lbr If occ is not greater than 0 or start or group are less than 0, the result is a null value. pcre: Perl Compatible Regular Expression (PCRE) \lbr \lbr sql_exp: see below \lbr \lbrocc: 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 \lbr \lbr case: 'X' or ' ' \lbr \lbr start: i or int8 \lbr \lbr 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.

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

Notes

  • 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, which produces a syntax error. In general, the maximum possible length is calculated by dividing the length of sql_exp1 by the length of sql_exp2, multiplied by the length of sql_exp3.
  • All string functions with the result type SSTRING can have a maximum of 1333 characters. This length restriction can be avoided by using the type conversion function TO_CLOB on the result, thus converting it to type STRING.
  • ABAP SQL string functions enforce the strict mode of the syntax check from Release .
  • The functions that include the pcre parameter access the PCRE1 library implemented in the SAP HANA database. They enforce the strict mode of the syntax check from Release . The regular expressions of general ABAP work with the PCRE2 library implemented in the ABAP Kernel.

Example

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

Example

Concatenation of multiple columns of a DDIC database table to a character-like column in the program 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 &&.






SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up   PERFORM Short Reference  
This documentation is copyright by SAP AG.

Length: 36834 Date: 20240329 Time: 075255     sap01-206 ( 931 ms )