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:
- Aggregate functions (Table 1)
- Array functions (Table 2)
- Cast scalar functions (Table 3)
- Datetime scalar functions (Table 4)
- JSON scalar functions (Table 5)
- Miscellaneous scalar functions (Table 6)
- Numeric scalar functions (Table 7)
- Partitioning scalar functions (Table 8)
- Regular expression functions (Table 9)
- Security scalar functions (Table 10)
- String scalar functions (Table 11)
- Table functions (Table 12)
- XML functions (Table 13)
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
Supported built-in SQL routines and viewsin Administrative Routines and Views.
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:
|
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. |
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. |
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. |
BPCHAR scalar function | Returns a VARCHAR representation of a value. |
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. |
DATETIME scalar function | Returns a TIMESTAMP from a value or a pair of values. |
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 functionsEMPTY_BLOB, EMPTY_CLOB, and EMPTY_DBCLOB scalar functions | Return a zero-length value of the associated data type. |
FLOAT scalar function | Returns a DOUBLE representation of a value. |
FLOAT4 scalar function | Returns a REAL representation of a value. |
FLOAT8 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. |
INT2 scalar function | Returns a SMALLINT representation of a value. |
INT4 scalar function | Returns a INTEGER representation of a value. |
INT8 scalar function | Returns a BIGINT 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. |
NUMERIC scalar function | Returns a DECIMAL representation of a value. |
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. |
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. |
TIMEZONE scalar function | Converts a date and time in one timezone into a timestamp in another timezone. |
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. |
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. |
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. |
ISFALSE, ISTRUE, ISNOTFALSE, and ISNOTTRUE scalar functions | Each of these functions returns a Boolean value (TRUE or FALSE) depending on the truth value of its input expression. |
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. |
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. |
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. |
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. |
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 functionVERIFY_GROUP_FOR_USER | 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 functionVERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER | 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. |
Function | Description |
---|---|
ASCII scalar function | Returns the ASCII code value of the leftmost character of the argument as an integer. |
ASCII_STR scalar function | Returns an ASCII version of the string. |
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. |
QUOTE_IDENT scalar function | Returns a string that can be used as an identifier in an SQL statement. |
QUOTE_LITERAL scalar function | Returns a string that can be used as a string constant in an SQL statement. |
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 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_MULTI_BYTE scalar function | Returns a Unicode string in which the single-byte characters in a string-expression are converted to their multi-byte (full width) equivalents. |
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. |
UNICODE_STR scalar function | Returns a string in Unicode UTF-8 or UTF-16, depending on the specified option. The string represents a Unicode encoding of the input string. |
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. |
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. |
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. |