Built-in functions

Built-in functions are functions provided with the database manager and are classified as aggregate functions, scalar functions, or table functions.

This topic lists the supported built-in functions classified by type:
The OLAP specification topic documents the following OLAP functions which are sometimes referred to as built-in functions:
  • FIRST_VALUE and LAST_VALUE
  • LAG and LEAD
  • NTILE
  • RANK and DENSE_RANK
  • RATIO_TO_REPORT
  • ROW_NUMBER
There are additional built-in functions documented under the following headings:
  • ADMIN_CMD procedure and associated SQL routines
  • Audit routines and procedures
  • Configuration SQL routines and views
  • Db2® pureScale® instance views
  • Environment views
  • Explain routines
  • Monitor routines
  • MQSeries® SQL routines
  • Security SQL routines and views
  • Snapshot SQL routines and views
  • SQL procedures SQL routines
  • Workload management routines
  • Miscellaneous SQL routines and views
For details about these additional built-in functions, see Supported built-in SQL routines and views.
Table 1. Aggregate functions
Function Description
ARRAY_AGG aggregate function Aggregates a set of elements into an array.
AVG aggregate function Returns the average of a set of numbers.
CORRELATION aggregate function Returns the coefficient of correlation of a set of number pairs.
COUNT aggregate function Returns the number of rows or values in a set of rows or values.
COUNT_BIG aggregate function Returns the number of rows or values in a set of rows or values. The result can be greater than the maximum value of INTEGER.
COVARIANCE aggregate function Returns the covariance of a set of number pairs.
COVARIANCE_SAMP aggregate function Returns the sample covariance of a set of number pairs.
CUME_DIST aggregate function Returns the cumulative distribution of a row that is hypothetically inserted into a group of rows.
GROUPING aggregate function Used with grouping-sets and super-groups to indicate sub-total rows generated by a grouping set. The value returned is 0 or 1. A value of 1 means that the value of the argument in the returned row is a null value, and the row was generated for a grouping set. This generated row provides a sub-total for a grouping set.
LISTAGG aggregate function Aggregates a set string elements into one string by concatenating the strings.
MAX aggregate function Returns the maximum value in a set of values.
MEDIAN aggregate function Returns the median value in a set of values.
MIN aggregate function Returns the minimum value in a set of values.
PERCENTILE_CONT aggregate function Returns the value that corresponds to the specified percentile, given a sort specification by using a continuous distribution model.
PERCENTILE_DISC aggregate function Returns the value that corresponds to the specified percentile given a sort specification by using a discrete distribution model.
PERCENT_RANK aggregate function Returns the relative percentile rank of a row that is hypothetically inserted into a group of rows.
Regression functions (REGR_AVGX, REGR_AVGY, REGR_COUNT, ...) The regression functions fit an ordinary-least-squares regression line of the form y = a * x + b to a set of number pairs:
  • REGR_AVGX returns quantities used to compute diagnostic statistics.
  • REGR_AVGY returns quantities used to compute diagnostic statistics.
  • REGR_COUNT returns the number of non-null number pairs used to fit the regression line.
  • REGR_INTERCEPT or REGR_ICPT returns the y-intercept of the regression line.
  • REGR_R2 returns the coefficient of determination for the regression.
  • REGR_SLOPE returns the slope of the line.
  • REGR_SXX returns quantities used to compute diagnostic statistics.
  • REGR_SXY returns quantities used to compute diagnostic statistics.
  • REGR_SYY returns quantities used to compute diagnostic statistics.
STDDEV aggregate function Returns the biased standard deviation (division by n) of a set of numbers.
STDDEV_SAMP aggregate function Returns the sample standard deviation (division by [n-1]) of a set of numbers.
SUM aggregate function Returns the sum of a set of numbers.
VARIANCE aggregate function Returns the biased variance (division by n) of a set of numbers.
VARIANCE_SAMP aggregate function Returns the sample variance (division by [n-1]) of a set of numbers.
XMLAGG aggregate function Returns an XML sequence containing an item for each non-null value in a set of XML values.
XMLGROUP aggregate function Returns an XML value with a single XQuery document node containing one top-level element node.
Table 2. Array functions
Function Description
ARRAY_AGG aggregate function Aggregates a set of elements into an array.
ARRAY_DELETE scalar function Deletes an element or range of elements from an associative array.
ARRAY_FIRST scalar function Returns the smallest array index value of the array.
ARRAY_LAST scalar function Returns the largest array index value of the array.
ARRAY_NEXT scalar function Returns the next larger array index value for an array relative to the specified array index argument.
ARRAY_PRIOR scalar function Returns the next smaller array index value for an array relative to the specified array index argument.
CARDINALITY scalar function Returns a value of type BIGINT representing the number of elements of an array
MAX_CARDINALITY scalar function Returns a value of type BIGINT representing the maximum number of elements that an array can contain.
TRIM_ARRAY scalar function Returns a value with the same array type as array-variable but with the cardinality reduced by the value of numeric-expression.
UNNEST table function Returns a result table that includes a row for each element of the specified array.
Table 3. Cast scalar functions
Function Description
BIGINT scalar function Returns a 64-bit integer representation of a value in the form of an integer constant.
BINARY scalar function Returns a fixed-length binary string representation of a string of any data type.
BLOB scalar function Returns a BLOB representation of a string of any type.
CHAR scalar function Returns a CHARACTER representation of a value.
CLOB scalar function Returns a CLOB representation of a value.
DATE scalar function Returns a DATE from a value.
DBCLOB scalar function Returns a DBCLOB representation of a string.
DECFLOAT scalar function Returns the decimal floating-point representation of a value.
DECIMAL or DEC scalar function Returns a DECIMAL representation of a value.
DOUBLE_PRECISION or DOUBLE scalar function

Returns the floating-point representation of a value.
EMPTY_BLOB, EMPTY_CLOB, EMPTY_DBCLOB, and EMPTY_NCLOB scalar functions Return a zero-length value of the associated data type.
FLOAT scalar function Returns a DOUBLE representation of a value.
GRAPHIC scalar function Returns a GRAPHIC representation of a string.
INT scalar function Returns an INTEGER representation of a value.
INTEGER scalar function Returns an INTEGER representation of a value.
NCHAR scalar function Returns a fixed-length national character string representation of a value.
NCLOB scalar function Returns an NCLOB representation of a national character string.
NVARCHAR scalar function Returns a varying-length national character string representation of a value.
REAL scalar function

Returns the single-precision floating-point representation of a value.
SMALLINT scalar function Returns a SMALLINT representation of a value.
TIME scalar function Returns a TIME from a value.
TIMESTAMP scalar function Returns a TIMESTAMP from a value or a pair of values.
TO_CLOB scalar function Returns a CLOB representation of a character string type.
TO_NCLOB scalar function Returns an NCLOB representation of a character string.
VARBINARY scalar function Returns a VARBINARY (varying-length binary string) representation of a string of any data type.
VARCHAR scalar function Returns a VARCHAR representation of a value.
VARGRAPHIC scalar function Returns a VARGRAPHIC representation of a value.
Table 4. Datetime scalar functions
Function Description
ADD_DAYS scalar function Returns a datetime value that represents the first argument plus a specified number of days.
ADD_HOURS scalar function Returns a timestamp value that represents the first argument plus a specified number of hours.
ADD_MINUTES scalar function Returns a timestamp value that represents the first argument plus a specified number of minutes.
ADD_MONTHS scalar function Returns a datetime value that represents expression plus a specified number of months.
ADD_SECONDS scalar function Returns a timestamp value that represents the first argument plus a specified number of seconds and fractional seconds.
ADD_YEARS scalar function Returns a datetime value that represents the first argument plus a specified number of years.
AGE scalar function Returns a numeric value that specifies the number of full years, full months, and full days between the current timestamp and the argument.
DATE_PART scalar function Returns portion of a datetime based on its argument.
DATE_TRUNC scalar function Returns a timestamp expression rounded to the specified unit.
DAY scalar function Returns the day part of a value.
DAYNAME scalar function Returns a character string containing the name of the day (for example, Friday) for the day portion of expression, based on locale-name or the value of the special register CURRENT LOCALE LC_TIME.
DAYOFMONTH scalar function Returns an integer between 1 and 31 that represents the day of the month.
DAYOFWEEK scalar function Returns the day of the week in the first argument as an integer value. The integer value is in the range 1-7, where 1 represents the first day of the week, as specified in the second argument.
DAYOFWEEK_ISO scalar function Returns the day of the week from a value, where 1 is Monday and 7 is Sunday.
DAYOFYEAR scalar function Returns the day of the year from a value.
DAYS scalar function Returns an integer representation of a date.
DAYS_BETWEEN scalar function Returns the number of full days between the specified arguments.
DAYS_TO_END_OF_MONTH scalar function Returns the number of days to the end of the month.
EXTRACT scalar function Returns a portion of a date or timestamp based on the arguments.
FIRST_DAY scalar function Returns a date or timestamp that represents the first day of the month of the argument.
FROM_UTC_TIMESTAMP scalar function Returns a TIMESTAMP that is converted from Coordinated Universal Time to the timezone that is specified by the timezone string.
HOUR scalar function Returns the hour part of a value.
HOURS_BETWEEN scalar function Returns the number of full hours between the specified arguments.
INTERVAL scalar function Returns a DECIMAL duration that corresponds to a duration specified as a string.
JULIAN_DAY scalar function Returns an integer value representing the number of days from January 1, 4712 B.C. to the date specified in the argument.
LAST_DAY scalar function Returns a datetime value that represents the last day of the month of the argument.
MICROSECOND scalar function Returns the microsecond part of a value.
MIDNIGHT_SECONDS scalar function Returns an integer value representing the number of seconds between midnight and a specified time value.
MINUTE scalar function Returns the minute part of a value.
MINUTES_BETWEEN scalar function Returns the number of full minutes between the specified arguments.
MONTH scalar function Returns the month part of a value.
MONTHNAME scalar function Returns a character string containing the name of the month (for example, January) for the month portion of expression, based on locale-name or the value of the special register CURRENT LOCALE LC_TIME.
MONTHS_BETWEEN scalar function Returns an estimate of the number of months between expression1 and expression2.
NEXT_DAY scalar function Returns a datetime value that represents the first weekday, named by string-expression, that is later than the date in expression.
NEXT_MONTH scalar function Returns the first day of the next month after the specified date.
NEXT_QUARTER scalar function Returns the first day of the next quarter after the specified date.
NEXT_WEEK scalar function Returns the first day of the next week after the specified date.
NEXT_YEAR scalar function Returns the first day of the next year after the specified date.
NOW scalar function Returns a timestamp based on when the SQL statement is executed at the current server.
QUARTER scalar function Returns an integer that represents the quarter of the year in which a date resides.
ROUND scalar function Returns a datetime value, rounded to the unit specified by format-string.
ROUND_TIMESTAMP scalar function Returns a timestamp that is the expression rounded to the unit specified by the format-string.
SECOND scalar function Returns the seconds part of a value.
SECONDS_BETWEEN scalar function Returns the number of full seconds between the specified arguments.
THIS_MONTH scalar function Returns the first day of the month in the specified date.
THIS_QUARTER scalar function Returns the first day of the quarter in the specified date.
THIS_WEEK scalar function Returns the first day of the week in the specified date.
THIS_YEAR scalar function Returns the first day of the year in the specified date.
TIMESTAMP_FORMAT scalar function Returns a timestamp from a character string (argument1) that has been interpreted using a format template (argument2).
TIMESTAMP_ISO scalar function Returns a timestamp value based on a date, time, or timestamp argument. If the argument is a date, it inserts zero for all the time elements. If the argument is a time, it inserts the value of CURRENT DATE for the date elements, and zero for the fractional time element.
TIMESTAMPDIFF scalar function Returns an estimated number of intervals of type argument1, based on the difference between two timestamps. The second argument is the result of subtracting two timestamp types and converting the result to CHAR.
TO_CHAR scalar function Returns a CHARACTER representation of a timestamp.
TO_DATE scalar function Returns a timestamp from a character string.
TO_NCHAR scalar function Returns a national character representation of an input expression that has been formatted using a character template.
TO_TIMESTAMP scalar function Returns a timestamp that is based on the interpretation of the input string using the specified format.
TO_UTC_TIMESTAMP scalar function Returns a TIMESTAMP that is converted from Coordinated Universal Time to the timezone specified by the timezone string.
TRUNCATE or TRUNC scalar function Returns a datetime value, truncated to the unit specified by format-string.
TRUNC_TIMESTAMP scalar function Returns a timestamp that is the expression truncated to the unit specified by the format-string.
VARCHAR_FORMAT scalar function Returns a CHARACTER representation of a timestamp (argument1), formatted according to a template (argument2).
WEEK scalar function Returns the week of the year from a value, where the week starts with Sunday.
WEEK_ISO scalar function Returns the week of the year from a value, where the week starts with Monday.
WEEKS_BETWEEN scalar function Returns the number of full weeks between the specified arguments.
YEAR scalar function Returns the year part of a value.
YEARS_BETWEEN scalar function Returns the number of full years between the specified arguments.
YMD_BETWEEN scalar function Returns a numeric value that specifies the number of full years, full months, and full days between two datetime values.
Table 5. JSON scalar functions
Function Description
BSON_TO_JSON scalar function Converts a string that contains data that is formatted as BSON to a character string that contains data that is formatted as JSON.
JSON_ARRAY scalar function Generates a JSON array by explicitly listing the array elements by using an expression or by using a query.
JSON_OBJECT scalar function Generates a JSON object by using the specified key:value pairs. If no key:value pairs are provided, an empty object is returned.
JSON_QUERY scalar function Returns an SQL/JSON value from the specified JSON text by using an SQL/JSON path expression.
JSON_TO_BSON scalar function Converts a string that contains data that is formatted for JSON to a binary string that contains data that is formatted as BSON.
JSON_VALUE scalar function Returns an SQL scalar value from JSON text, by using an SQL/JSON path expression.
Table 6. Miscellaneous scalar functions
Function Description
BITAND, BITANDNOT, BITOR, BITXOR, and BITNOT scalar functions These bitwise functions operate on the "two's complement" representation of the integer value of the input arguments and return the result as a corresponding base 10 integer value in a data type based on the data type of the input arguments.
COALESCE scalar function Returns the first argument that is not null.
CURSOR_ROWCOUNT scalar function Returns the cumulative count of all rows fetched by the specified cursor since the cursor was opened.
DECODE scalar function Compares each specified expression2 to expression1. If expression1 is equal to expression2, or both expression1 and expression2 are null, the value of the following result-expresssion is returned. If no expression2 matches expression1, the value of else-expression is returned; otherwise a null value is returned.
DEREF scalar function Returns an instance of the target type of the reference type argument.
EVENT_MON_STATE scalar function Returns the operational state of particular event monitor.
GREATEST scalar function Returns the maximum value in a set of values.
HEX scalar function Returns a hexadecimal representation of a value.
IDENTITY_VAL_LOCAL scalar function Returns the most recently assigned value for an identity column.
INTNAND, INTNOR, INTNXOR, and INTNNOT scalar functions These bitwise functions operate on the "two's complement" representation of the integer value of the input arguments and return the result as a corresponding base 10 integer value.
LEAST scalar function Returns the minimum value in a set of values.
LENGTH scalar function Returns the length of a value.
MAX scalar function Returns the maximum value in a set of values.
MIN scalar function Returns the minimum value in a set of values.
NULLIF scalar function Returns a null value if the arguments are equal; otherwise, it returns the value of the first argument.
NVL scalar function Returns the first argument that is not null.
RAISE_ERROR scalar function Raises an error in the SQLCA. The sqlstate that is to be returned is indicated by argument1. The second argument contains any text that is to be returned.
RAWTOHEX scalar function Returns a hexadecimal representation of a value as a character string.
REC2XML scalar function Returns a string formatted with XML tags, containing column names and column data.
RID and RID_BIT scalar functions The RID_BIT scalar function returns the row identifier (RID) of a row in a character string format. The RID scalar function returns the RID of a row in large integer format. The RID function is not supported in partitioned database environments. The RID_BIT function is preferred over the RID function.
TABLE_NAME scalar function Returns an unqualified name of a table or view based on the object name specified in argument1, and the optional schema name specified in argument2. The returned value is used to resolve aliases.
TABLE_SCHEMA scalar function Returns the schema name portion of a two-part table or view name (given by the object name in argument1 and the optional schema name in argument2). The returned value is used to resolve aliases.
TO_HEX scalar function Converts a numeric expression into the hexadecimal representation.
TYPE_ID scalar function Returns the internal data type identifier of the dynamic data type of the argument. The result of this function is not portable across databases.
TYPE_NAME scalar function Returns the unqualified name of the dynamic data type of the argument.
TYPE_SCHEMA scalar function Returns the schema name of the dynamic data type of the argument.
VALUE scalar function Returns the first argument that is not null.
Table 7. Numeric scalar functions
Function Description
ABS or ABSVAL scalar function Returns the absolute value of a number.
ACOS scalar function Returns the arc cosine of a number, in radians.
ASIN scalar function Returns the arc sine of a number, in radians.
ATAN scalar function Returns the arc tangent of a number, in radians.
ATANH scalar function Returns the hyperbolic arc tangent of a number, in radians.
ATAN2 scalar function Returns the arc tangent of x and y coordinates as an angle expressed in radians.
CEILING or CEIL scalar function Returns the smallest integer value that is greater than or equal to a number.
COMPARE_DECFLOAT scalar function Returns a SMALLINT value that indicates whether the two arguments are equal or unordered, or whether one argument is greater than the other.
COS scalar function Returns the cosine of a number.
COSH scalar function Returns the hyperbolic cosine of a number.
COT scalar function Returns the cotangent of the argument, where the argument is an angle expressed in radians.
DECFLOAT_FORMAT scalar function Returns a DECFLOAT(34) from a character string.
DEGREES scalar function Returns the number of degrees of an angle.
DIGITS scalar function Returns a character-string representation of the absolute value of a number.
EXP scalar function Returns a value that is the base of the natural logarithm (e) raised to a power specified by the argument.
FLOOR scalar function Returns the largest integer value that is less than or equal to a number.
LN scalar function Returns the natural logarithm of a number.
LOG10 scalar function Returns the common logarithm (base 10) of a number.
MOD scalar function Returns the remainder of the first argument divided by the second argument.
MOD (SYSFUN schema) scalar function Returns the remainder of the first argument divided by the second argument.
MULTIPLY_ALT scalar function Returns the product of two arguments as a decimal value. This function is useful when the sum of the argument precisions is greater than 31.
NORMALIZE_DECFLOAT scalar function Returns a decimal floating-point value that is the result of the argument set to its simplest form.
POW scalar function Returns the result of raising the first argument to the power of the second argument.
POWER scalar function Returns the result of raising the first argument to the power of the second argument.
QUANTIZE scalar function Returns a decimal floating-point number that is equal in value and sign to the first argument, and whose exponent is equal to the exponent of the second argument.
RADIANS scalar function Returns the number of radians for an argument that is expressed in degrees.
RANDOM scalar function Returns a floating point value between 0 and 1.
RAND (SYSFUN schema) scalar function Returns a random number.
RAND (SYSIBM schema) scalar function Returns a floating point value between 0 and 1.
ROUND scalar function Returns a numeric value that has been rounded to the specified number of decimal places.
SIGN scalar function Returns the sign of a number.
SIN scalar function Returns the sine of a number.
SINH scalar function Returns the hyperbolic sine of a number.
SQRT scalar function Returns the square root of a number.
TAN scalar function Returns the tangent of a number.
TANH scalar function Returns the hyperbolic tangent of a number.
TO_NUMBER scalar function Returns a DECFLOAT(34) from a character string.
TOTALORDER scalar function Returns a SMALLINT value of -1, 0, or 1 that indicates the comparison order of two arguments.
TRUNCATE or TRUNC scalar function Returns a number value that has been truncated at a specified number of decimal places.
VARCHAR_FORMAT scalar function Returns a CHARACTER representation of a timestamp (argument1), formatted according to a template (argument2).
WIDTH_BUCKET scalar function Creates equal-width histograms.
Table 8. Partitioning scalar functions
Function Description
DATAPARTITIONNUM scalar function Returns the sequence number (SYSDATAPARTITIONS.SEQNO) of the data partition in which the row resides. The argument is any column name within the table.
DBPARTITIONNUM scalar function Returns the database partition number of the row. The argument is any column name within the table.
HASH scalar function Returns the 128-bit, 160-bit, 256-bit or 512-bit hash of the input data.
HASH4 scalar function Returns the 32-bit hash of the input data.
HASH8 scalar function Returns the 64-bit hash of the input data.
HASHEDVALUE scalar function Returns the distribution map index (0 to 32767) of the row. The argument is a column name within a table.
Table 9. Regular expression scalar functions
Function Description
REGEXP_COUNT scalar function Returns a count of the number of times that a regular expression pattern is matched in a string.
REGEXP_EXTRACT scalar function Returns one occurrence of a substring of a string that matches the regular expression pattern.
REGEXP_INSTR scalar function Returns the starting or ending position of the matched substring, depending on the value of the return_option argument.
REGEXP_LIKE scalar function Returns a boolean value indicating if the regular expression pattern is found in a string. The function can be used only where a predicate is supported.
REGEXP_MATCH_COUNT scalar function Returns a count of the number of times that a regular expression pattern is matched in a string.
REGEXP_REPLACE scalar function Returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string.
REGEXP_SUBSTR scalar function Returns one occurrence of a substring of a string that matches the regular expression pattern.
Table 10. Security scalar functions
Function Description
SECLABEL scalar function Returns an unnamed security label.
SECLABEL_BY_NAME scalar function Returns a specific security label.
SECLABEL_TO_CHAR scalar function Accepts a security label and returns a string that contains all elements in the security label.
VERIFY_GROUP_FOR_USER scalar function Returns a value that indicates whether any of the groups associated with authorization-id-expression are in the group names specified by the list of group-name-expression arguments.
VERIFY_ROLE_FOR_USER scalar function Returns a value that indicates whether any of the roles associated with authorization-id-expression are in, or contain any of, the role names specified by the list of role-name-expression arguments.
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER scalar function Returns a value that indicates whether authorization-id-expression has acquired a role under a trusted connection associated with some trusted context and that role is in, or contained in any of, the role names specified by the list of role-name-expression arguments.
Table 11. String scalar functions
Function Description
ASCII scalar function Returns the ASCII code value of the leftmost character of the argument as an integer.
BTRIM scalar function Removes characters from the beginning and end of a string expression.
CHARACTER_LENGTH scalar function Returns the length of an expression in the specified string-unit.
CHR scalar function Returns the character that has the ASCII code value specified by the argument.
COLLATION_KEY scalar function Returns a VARBINARY string representing the collation key of the specified string-expression in the specified collation-name.
COLLATION_KEY_BIT scalar function Returns a VARCHAR FOR BIT DATA string representing the collation key of the specified string-expression in the specified collation-name.
CONCAT scalar function Returns a string that is the concatenation of two strings.
DECRYPT_BIN and DECRYPT_CHAR scalar functions Returns a value that is the result of decrypting encrypted data using a password string.
DIFFERENCE scalar function Returns the difference between the sounds of the words in two argument strings, as determined by the SOUNDEX function. A value of 4 means the strings sound the same.
ENCRYPT scalar function Returns a value that is the result of encrypting a data string expression.
GENERATE_UNIQUE scalar function Returns a bit data character string that is unique compared to any other execution of the same function.
GETHINT scalar function Returns the password hint if one is found.
INITCAP scalar function Returns a string with the first character of each word converted to uppercase and the rest to lowercase.
INSERT scalar function Returns a string, where argument3 bytes have been deleted from argument1 (beginning at argument2), and argument4 has been inserted into argument1 (beginning at argument2).
INSTR scalar function Returns the starting position of a string within another string.
INSTR2 scalar function Returns the starting position, in 16-bit UTF-16 string units (CODEUNITS16), of a string within another string.
INSTR4 scalar function Returns the starting position, in 32-bit UTF-32 string units (CODEUNITS32), of a string within another string.
INSTRB scalar function Returns the starting position, in bytes, of a string within another string.
LCASE scalar function Returns a string in which all the SBCS characters have been converted to lowercase characters.
LCASE (locale sensitive) scalar function Returns a string in which all characters have been converted to lowercase characters using the rules from the Unicode standard associated with the specified locale.
LCASE (SYSFUN schema) scalar function Returns a string in which all the SBCS characters have been converted to lowercase characters.
LEFT scalar function Returns the leftmost characters from a string.
LENGTH scalar function Returns the length of expression in the implicit or explicit string unit.
LENGTH2 scalar function Returns the length of expression in 16-bit UTF-16 string units (CODEUNITS16).
LENGTH4 scalar function Returns the length of expression in 32-bit UTF-32 string units (CODEUNITS32).
LENGTHB scalar function Returns the length of expression in bytes.
LOCATE scalar function Returns the starting position of one string within another string.
LOCATE_IN_STRING scalar function Returns the starting position of the first occurrence of one string within another string.
LOWER scalar function Returns a string in which all the characters have been converted to lowercase characters.
LOWER (locale sensitive) scalar function Returns a string in which all characters have been converted to lowercase characters using the rules from the Unicode standard associated with the specified locale.
LPAD scalar function Returns a string that is padded on the left with the specified character, or with blanks.
LTRIM scalar function Removes blanks from the beginning of a string expression.
LTRIM (SYSFUN schema) scalar function Removes blanks from the beginning of a string expression.
OCTET_LENGTH scalar function Returns the length of an expression in octets (bytes).
OVERLAY scalar function Returns a string in which, beginning at start in the specified source-string, length of the specified code units have been deleted and insert-string has been inserted.
POSITION scalar function Returns the starting position of argument2 within argument1.
POSSTR scalar function Returns the starting position of one string within another string.
REPEAT scalar function Returns a character string composed of the first argument repeated the number of times specified by the second argument.
REPEAT (SYSFUN schema) scalar function Returns a character string composed of the first argument repeated the number of times specified by the second argument.
REPLACE scalar function Replaces all occurrences of argument2 in argument1 with argument3.
REPLACE (SYSFUN schema) scalar function Replaces all occurrences of expres sion2 in expression1 with e xpression3.
RIGHT scalar function Returns the rightmost characters from a string.
RPAD scalar function Returns a string that is padded on the right with the specified character, string, or with blanks.
RTRIM scalar function Removes blanks from the end of a string expression.
RTRIM (SYSFUN schema) scalar function Removes blanks from the end of a string expression.
SOUNDEX scalar function Returns a 4-character code representing the sound of the words in the argument. This result can be compared with the sound of other strings.
SPACE scalar function Returns a character string that consists of a specified number of blanks.
STRIP scalar function Removes blanks or another specified character from the end, the beginning, or both ends of a string expression.
STRLEFT scalar function Returns the leftmost string of string-expression of length length, expressed in the specified string unit.
STRPOS scalar function Returns the starting position of one string within another string.
STRRIGHT scalar function Returns the rightmost string of string-expression of length length, expressed in the specified string unit.
SUBSTR scalar function Returns a substring of a string.
SUBSTR2 scalar function Returns a substring of a string. The start and length arguments are expressed in 16-bit UTF-16 string units (CODEUNITS16).
SUBSTR4 scalar function Returns a substring of a string. The start and length arguments are expressed in 32-bit UTF-32 string units (CODEUNITS32).
SUBSTRB scalar function Returns a substring of a string. The start and length arguments are expressed in bytes.
SUBSTRING scalar function Returns a substring of a string.
TO_SINGLE_BYTE scalar function Returns a string in which multi-byte characters are converted to the equivalent single-byte character where an equivalent character exists.
TRANSLATE scalar function Returns a string in which one or more characters in a string are converted to other characters.
TRIM scalar function Removes blanks or another specified character from the end, the beginning, or both ends of a string expression.
UCASE scalar function The UCASE function is identical to the TRANSLATE function except that only the first argument (char-string-exp) is specified.
UCASE (locale sensitive) scalar function Returns a string in which all characters have been converted to uppercase characters using the rules from the Unicode standard associated with the specified locale.
UPPER scalar function Returns a string in which all the characters have been converted to uppercase characters.
UPPER (locale sensitive) scalar function Returns a string in which all characters have been converted to uppercase characters using the rules from the Unicode standard associated with the specified locale.
Table 12. Table functions
Function Description
BASE_TABLE table function Returns both the object name and schema name of the object found after any alias chains have been resolved.
JSON_TABLE table function Returns a result table from the evaluation of SQL/JSON path expressions. Each item in the result sequence of the row SQL/JSON path expression represents one or more rows in the result table.
UNNEST table function Returns a result table that includes a row for each element of the specified array.
XMLTABLE table function Returns a table from the evaluation of XQuery expressions, possibly using specified input arguments as XQuery variables. Each sequence item in the result sequence of the row XQuery expression represents a row of the result table.
Table 13. XML functions
Function Description
PARAMETER scalar function Represents a position in an SQL statement where the value is provided dynamically by XQuery as part of the invocation of the db2-fn:sqlquery function.
XMLAGG aggregate function Returns an XML sequence containing an item for each non-null value in a set of XML values.
XMLATTRIBUTES scalar function Constructs XML attributes from the arguments.
XMLCOMMENT scalar function Returns an XML value with a single XQuery comment node with the input argument as the content.
XMLCONCAT scalar function Returns a sequence containing the concatenation of a variable number of XML input arguments.
XMLDOCUMENT scalar function Returns an XML value with a single XQuery document node with zero or more children nodes.
XMLELEMENT scalar function Returns an XML value that is an XML element node.
XMLFOREST scalar function Returns an XML value that is a sequence of XML element nodes.
XMLGROUP aggregate function Returns an XML value with a single XQuery document node containing one top-level element node.
XMLNAMESPACES declaration Constructs namespace declarations from the arguments.
XMLPARSE scalar function Parses the argument as an XML document and returns an XML value.
XMLPI scalar function Returns an XML value with a single XQuery processing instruction node.
XMLQUERY scalar function Returns an XML value from the evaluation of an XQuery expression possibly using specified input arguments as XQuery variables.
XMLROW scalar function Returns an XML value with a single XQuery document node containing one top-level element node.
XMLSERIALIZE scalar function Returns a serialized XML value of the specified data type generated from the argument.
XMLTABLE table function Returns a table from the evaluation of XQuery expressions, possibly using specified input arguments as XQuery variables. Each sequence item in the result sequence of the row XQuery expression represents a row of the result table.
XMLTEXT scalar function Returns an XML value with a single XQuery text node having the input argument as the content.
XMLVALIDATE scalar function Returns a copy of the input XML value augmented with information obtained from XML schema validation, including default values.
XMLXSROBJECTID scalar function Returns the XSR object identifier of the XML schema used to validate the XML document that is specified in the argument
XSLTRANSFORM scalar function Converts XML data into other formats, including the conversion of XML documents that conform to one XML schema into documents that conform to another schema.