SQL Reference

Functions

A function is an operation that is denoted by a function name followed by a pair of parentheses enclosing the specification of arguments (there may be no arguments).

Functions are classified as column functions, scalar functions, row functions or table functions.

• The argument of a column function is a collection of like values. It returns a single value (possibly null), and can be specified in an SQL statement where an expression can be used. Additional restrictions apply to the use of column functions as specified in Column Functions.
• The argument(s) of a scalar function are individual scalar values, which can be of different types and have different meanings. It returns a single value (possibly null), and can be specified in an SQL statement wherever an expression can be used.
• The argument of a row function is a structured type. It returns a row of built-in data types and can only be specified as a transform function for a structured type.
• The argument(s) of a table function are individual scalar values, which can be of different types and have different meanings. It returns a table to the SQL statement, and can be specified only within the FROM clause of a SELECT. Additional restrictions apply to the use of table functions as specified in from-clause.

Table 15 shows the functions that are supported. The "Function Name" combined with the "Schema" give the fully qualified name of the function. "Description" briefly describes what the function does. "Input Parameters" gives the data type that is expected for each argument during function invocation. Many of the functions include variations of the input parameters allowing either different data types or different numbers of arguments to be used. The combination of schema, function name and input parameters make up a function signature. Each function signature may return a value of a different type which is shown in the "Returns" columns.

There are some distinctions that should be understood about the input parameter types. In some cases the type is specified as a specific built-in data type and in other cases it will use a general variable like any-numeric-type. When a specific data type is listed, this means that an exact match will only occur with the specified data type. When a general variable is used, each of the data types associated with that variable will result in an exact match. This distinction impacts function selection as described in Function Resolution.

There may be additional functions available because user-defined functions may be created in different schemas using one of these function signatures as a source (see CREATE FUNCTION for details) or users may create external functions using their own programs.

Note:

Built-in functions are provided with the database manager, providing a single result value, and they are identified as part of the SYSIBM schema. Examples of such functions include column functions such as AVG, operator functions such as "+", casting functions such as DECIMAL, and others such as SUBSTR. User-defined functions are functions that are registered to a database in SYSCAT.FUNCTIONS (using the CREATE FUNCTION statement). User-defined functions are never part of the SYSIBM schema. One such set of functions is provided with the database manager in a schema called SYSFUN.

Table 15. Supported Functions

Function name	Schema	Description
	Input Parameters		Returns
ABS or ABSVAL	SYSFUN	Returns the absolute value of the argument.
	SMALLINT		SMALLINT
	INTEGER		INTEGER
	BIGINT		BIGINT
	DOUBLE		DOUBLE
ACOS	SYSFUN	Returns the arccosine of the argument as an angle expressed in radians.
	DOUBLE		DOUBLE
ASCII	SYSFUN	Returns the ASCII code value of the leftmost character of the argument as an integer.
	CHAR		INTEGER
	VARCHAR(4000)		INTEGER
	CLOB(1M)		INTEGER
ASIN	SYSFUN	Returns the arcsine of the argument as an angle, expressed in radians.
	DOUBLE		DOUBLE
ATAN	SYSFUN	Returns the arctangent of the argument as an angle, expressed in radians.
	DOUBLE		DOUBLE
ATAN2	SYSFUN	Returns the arctangent of x and y coordinates, specified by the first and second arguments respectively, as an angle, expressed in radians.
	DOUBLE, DOUBLE		DOUBLE
AVG	SYSIBM	Returns the average of a set of numbers (column function).
	numeric-type 4		numeric-type 1
BIGINT	SYSIBM	Returns a 64 bit integer representation of a number or character string in the form of an integer constant.
	numeric-type		BIGINT
	VARCHAR		BIGINT
BLOB	SYSIBM	Casts from source type to BLOB, with optional length.
	string-type		BLOB
	string-type, INTEGER		BLOB
CEIL or CEILING	SYSFUN	Returns the smallest integer greater than or equal to the argument.
	SMALLINT		SMALLINT
	INTEGER		INTEGER
	BIGINT		BIGINT
	DOUBLE		DOUBLE
CHAR	SYSIBM	Returns a string representation of the source type.
	character-type		CHAR
	character-type, INTEGER		CHAR(integer)
	datetime-type		CHAR
	datetime-type, keyword 2		CHAR
	SMALLINT		CHAR(6)
	INTEGER		CHAR(11)
	BIGINT		CHAR(20)
	DECIMAL		CHAR(2+precision)
	DECIMAL, VARCHAR		CHAR(2+precision)
CHAR	SYSFUN	Returns a character string representation of a floating-point number.
	DOUBLE		CHAR(24)
CHR	SYSFUN	Returns the character that has the ASCII code value specified by the argument. The value of the argument should be between 0 and 255; otherwise, the return value is null.
	INTEGER		CHAR(1)
CLOB	SYSIBM	Casts from source type to CLOB, with optional length.
	character-type		CLOB
	character-type, INTEGER		CLOB
COALESCE 3	SYSIBM	Returns the first non-null argument in the set of arguments.
	any-type, any-union-compatible-type, ...		any-type
CONCAT or ||	SYSIBM	Returns the concatenation of 2 string arguments.
	string-type, compatible-string-type		max string-type
CORRELATION or CORR	SYSIBM	Returns the coefficient of correlation of a set of number pairs.
	numeric-type, numeric-type		DOUBLE
COS	SYSFUN	Returns the cosine of the argument, where the argument is an angle expressed in radians.
	DOUBLE		DOUBLE
COT	SYSFUN	Returns the cotangent of the argument, where the argument is an angle expressed in radians.
	DOUBLE		DOUBLE
COUNT	SYSIBM	Returns the count of the number of rows in a set of rows or values (column function).
	any-builtin-type 4		INTEGER
COUNT_BIG	SYSIBM	Returns the number of rows or values in a set of rows or values (column function). Result can be greater than the maximum value of integer.
	any-builtin-type 4		DECIMAL(31,0)
COVARIANCE or COVAR	SYSIBM	Returns the covariance of a set of number pairs.
	numeric-type, numeric-type		DOUBLE
DATE	SYSIBM	Returns a date from a single input value.
	DATE		DATE
	TIMESTAMP		DATE
	DOUBLE		DATE
	VARCHAR		DATE
DAY	SYSIBM	Returns the day part of a value.
	VARCHAR		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
DAYNAME	SYSFUN	Returns a mixed case character string containing the name of the day (e.g. Friday) for the day portion of the argument based on what the locale was when db2start was issued.
	VARCHAR(26)		VARCHAR(100)
	DATE		VARCHAR(100)
	TIMESTAMP		VARCHAR(100)
DAYOFWEEK	SYSFUN	Returns the day of the week in the argument as an integer value in the range 1-7, where 1 represents Sunday.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
DAYOFWEEK_ISO	SYSFUN	Returns the day of the week in the argument as an integer value in the range 1-7, where 1 represents Monday.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
DAYOFYEAR	SYSFUN	Returns the day of the year in the argument as an integer value in the range 1-366.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
DAYS	SYSIBM	Returns an integer representation of a date.
	VARCHAR		INTEGER
	TIMESTAMP		INTEGER
	DATE		INTEGER
DBCLOB	SYSIBM	Casts from source type to DBCLOB, with optional length.
	graphic-type		DBCLOB
	graphic-type, INTEGER		DBCLOB
DECIMAL or DEC	SYSIBM	Returns decimal representation of a number, with optional precision and scale.
	numeric-type		DECIMAL
	numeric-type, INTEGER		DECIMAL
	numeric-type INTEGER, INTEGER		DECIMAL
DECIMAL or DEC	SYSIBM	Returns decimal representation of a character string, with optional precision, scale, and decimal-character.
	VARCHAR		DECIMAL
	VARCHAR, INTEGER		DECIMAL
	VARCHAR, INTEGER, INTEGER		DECIMAL
	VARCHAR, INTEGER, INTEGER, VARCHAR		DECIMAL
DEGREES	SYSFUN	Returns the number of degrees converted from the argument in expressed in radians.
	DOUBLE		DOUBLE
DEREF	SYSIBM	Returns an instance of the target type of the reference type argument.
	REF(any-structured-type) with defined scope		any-structured-type (same as input target type)
DIFFERENCE	SYSFUN	Returns the difference between the sounds of the words in the two argument strings as determined using the SOUNDEX function. A value of 4 means the strings sound the same.
	VARCHAR(4000), VARCHAR(4000)		INTEGER
DIGITS	SYSIBM	Returns the character string representation of a number.
	DECIMAL		CHAR
DLCOMMENT	SYSIBM	Returns the comment attribute of a datalink value.
	DATALINK		VARCHAR(254)
DLLINKTYPE	SYSIBM	Returns the link type attribute of a datalink value.
	DATALINK		VARCHAR(4)
DLURLCOMPLETE	SYSIBM	Returns the complete URL (including access token) of a datalink value.
	DATALINK		VARCHAR
DLURLPATH	SYSIBM	Returns the path and file name (including access token) of a datalink value.
	DATALINK		VARCHAR
DLURLPATHONLY	SYSIBM	Returns the path and file name (without any access token) of a datalink value.
	DATALINK		VARCHAR
DLURLSCHEME	SYSIBM	Returns the scheme from the URL attribute of a datalink value.
	DATALINK		VARCHAR
DLURLSERVER	SYSIBM	Returns the server from the URL attribute of a datalink value.
	DATALINK		VARCHAR
DLVALUE	SYSIBM	Builds a datalink value from a data-location argument, link type argument and optional comment-string argument.
	VARCHAR		DATALINK
	VARCHAR, VARCHAR		DATALINK
	VARCHAR, VARCHAR, VARCHAR		DATALINK
DOUBLE or DOUBLE_PRECISION	SYSIBM	Returns the floating-point representation of a number.
	numeric-type		DOUBLE
DOUBLE	SYSFUN	Returns the floating-point number corresponding to the character string representation of a number. Leading and trailing blanks in argument are ignored.
	VARCHAR		DOUBLE
EVENT_MON_STATE	SYSIBM	Returns the operational state of particular event monitor.
	VARCHAR		INTEGER
EXP	SYSFUN	Returns the exponential function of the argument.
	DOUBLE		DOUBLE
FLOAT	SYSIBM	Same as DOUBLE.
FLOOR	SYSFUN	Returns the largest integer less than or equal to the argument.
	SMALLINT		SMALLINT
	INTEGER		INTEGER
	BIGINT		BIGINT
	DOUBLE		DOUBLE
GENERATE_UNIQUE	SYSIBM	Returns a bit data character string that is unique compared to any other execution of the same function.
	no argument		CHAR(13) FOR BIT DATA
GRAPHIC	SYSIBM	Cast from source type to GRAPHIC, with optional length.
	graphic-type		GRAPHIC
	graphic-type, INTEGER		GRAPHIC
GROUPING	SYSIBM	Used with grouping-sets and super-groups to indicate sub-total rows generated by a grouping set (column function). The value returned is: 1 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. 0 otherwise.
	any-type		SMALLINT
HEX	SYSIBM	Returns the hexadecimal representation of a value.
	any-builtin-type		VARCHAR
HOUR	SYSIBM	Returns the hour part of a value.
	VARCHAR		INTEGER
	TIME		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
INSERT	SYSFUN	Returns a string where argument3 bytes have been deleted from argument1 beginning at argument2 and where argument4 has been inserted into argument1 beginning at argument2.
	VARCHAR(4000), INTEGER, INTEGER, VARCHAR(4000)		VARCHAR(4000)
	CLOB(1M), INTEGER, INTEGER, CLOB(1M)		CLOB(1M)
	BLOB(1M), INTEGER, INTEGER, BLOB(1M)		BLOB(1M)
INTEGER or INT	SYSIBM	Returns the integer representation of a number.
	numeric-type		INTEGER
	VARCHAR		INTEGER
JULIAN_DAY	SYSFUN	Returns an integer value representing the number of days from January 1, 4712 B.C. (the start of the Julian date calendar) to the date value specified in the argument.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
LCASE or LOWER	SYSIBM	Returns a string in which all the characters have been converted to lower case characters.
	CHAR		CHAR
	VARCHAR		VARCHAR
LCASE	SYSFUN	Returns a string in which all the characters have been converted to lower case characters. LCASE will only handle characters in the invariant set. Therefore, LCASE(UCASE(string)) will not necessarily return the same result as LCASE(string).
	VARCHAR(4000)		VARCHAR(4000)
	CLOB(1M)		CLOB(1M)
LEFT	SYSFUN	Returns a string consisting of the leftmost argument2 bytes in argument1.
	VARCHAR(4000), INTEGER		VARCHAR(4000)
	CLOB(1M), INTEGER		CLOB(1M)
	BLOB(1M), INTEGER		BLOB(1M)
LENGTH	SYSIBM	Returns the length of the operand in bytes (except for double byte string types which return the length in characters).
	any-builtin-type		INTEGER
LN	SUSFUN	Returns the natural logarithm of the argument (same as LOG).
	DOUBLE		DOUBLE
LOCATE	SYSFUN	Returns the starting position of the first occurrence of argument1 within argument2. If the optional third argument is specified, it indicates the character position in argument2 at which the search is to begin. If argument1 is not found within argument2, the value 0 is returned.
	VARCHAR(4000), VARCHAR(4000)		INTEGER
	VARCHAR(4000), VARCHAR(4000), INTEGER		INTEGER
	CLOB(1M), CLOB(1M)		INTEGER
	CLOB(1M), CLOB(1M), INTEGER		INTEGER
	BLOB(1M), BLOB(1M)		INTEGER
	BLOB(1M), BLOB(1M), INTEGER		INTEGER
LOG	SYSFUN	Returns the natural logarithm of the argument (same as LN).
	DOUBLE		DOUBLE
LOG10		Returns the base 10 logarithm of the argument.
	DOUBLE		DOUBLE
LONG_VARCHAR	SYSIBM	Returns a long string.
	character-type		LONG VARCHAR
LONG_VARGRAPHIC	SYSIBM	Casts from source type to LONG_VARGRAPHIC.
	graphic-type		LONG VARGRAPHIC
LTRIM	SYSIBM	Returns the characters of the argument with leading blanks removed.
	CHAR		VARCHAR
	VARCHAR		VARCHAR
	GRAPHIC		VARGRAPHIC
	VARGRAPHIC		VARGRAPHIC
LTRIM	SYSFUN	Returns the characters of the argument with leading blanks removed.
	VARCHAR(4000)		VARCHAR(4000)
	CLOB(1M)		CLOB(1M)
MAX	SYSIBM	Returns the maximum value in a set of values (column function).
	any-builtin-type 5		same as input type
MICROSECOND	SYSIBM	Returns the microsecond (time-unit) part of a value.
	VARCHAR		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
MIDNIGHT_SECONDS	SYSFUN	Returns an integer value in the range 0 to 86 400 representing the number of seconds between midnight and time value specified in the argument.
	VARCHAR(26)		INTEGER
	TIME		INTEGER
	TIMESTAMP		INTEGER
MIN	SYSIBM	Returns the minimum value in a set of values (column function).
	any-builtin-type 5		same as input type
MINUTE	SYSIBM	Returns the minute part of a value.
	VARCHAR		INTEGER
	TIME		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
MOD	SYSFUN	Returns the remainder ( modulus) of argument1 divided by argument2. The result is negative only if argument1 is negative.
	SMALLINT, SMALLINT		SMALLINT
	INTEGER, INTEGER		INTEGER
	BIGINT, BIGINT		BIGINT
MONTH	SYSIBM	Returns the month part of a value.
	VARCHAR		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
MONTHNAME	SYSFUN	Returns a mixed case character string containing the name of month (e.g. January) for the month portion of the argument that is a date or timestamp, based on what the locale was when the database was started.
	VARCHAR(26)		VARCHAR(100)
	DATE		VARCHAR(100)
	TIMESTAMP		VARCHAR(100)
NODENUMBER 3	SYSIBM	Returns the node number of the row. The argument is a column name within a table.
	any-type		INTEGER
NULLIF 3	SYSIBM	Returns NULL if the arguments are equal, else returns the first argument.
	any-type 5, any-comparable-type5		any-type
PARTITION 3	SYSIBM	Returns the partitioning map index (0 to 4095) of the row. The argument is a column name within a table.
	any-type		INTEGER
POSSTR	SYSIBM	Returns the position at which one string is contained in another.
	string-type, compatible-string-type		INTEGER
POWER	SYSFUN	Returns the value of argument1 to the power of argument2.
	INTEGER, INTEGER		INTEGER
	BIGINT, BIGINT		BIGINT
	DOUBLE, INTEGER		DOUBLE
	DOUBLE, DOUBLE		DOUBLE
QUARTER	SYSFUN	Returns an integer value in the range 1 to 4 representing the quarter of the year for the date specified in the argument.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
RADIANS	SYSFUN	Returns the number of radians converted from argument which is expressed in degrees.
	DOUBLE		DOUBLE
RAISE_ERROR3	SYSIBM	Raises an error in the SQLCA. The sqlstate returned is indicated by argument1. The second argument contains any text to be returned.
	VARCHAR, VARCHAR		any-type 6
RAND	SYSFUN	Returns a random floating point value between 0 and 1 using the argument as the optional seed value.
	no argument required		DOUBLE
	INTEGER		DOUBLE
REAL	SYSIBM	Returns the single-precision floating-point representation of a number.
	numeric-type		REAL
REGR_AVGX	SYSIBM	Returns quantities used to compute diagnostic statistics.
	numeric-type, numeric-type		DOUBLE
REGR_AVGY	SYSIBM	Returns quantities used to compute diagnostic statistics.
	numeric-type, numeric-type		DOUBLE
REGR_COUNT	SYSIBM	Returns the the number of non-null number pairs used to fit the regression line.
	numeric-type, numeric-type		INTEGER
REGR_INTERCEPT or REGR_ICPT	SYSIBM	Returns the y-intercept of the regression line.
	numeric-type, numeric-type		DOUBLE
REGR_R2	SYSIBM	Returns the coefficient of determination for the regression.
	numeric-type, numeric-type		DOUBE
REGR_SLOPE	SYSIBM	Returns the slope of the line.
	numeric-type, numeric-type		DOUBLE
REGR_SXX	SYSIBM	Returns quantities used to compute diagnostic statistics.
	numeric-type, numeric-type		DOUBLE
REGR_SXY	SYSIBM	Returns quantities used to compute diagnostic statistics.
	numeric-type, numeric-type		DOUBLE
REGR_SYY	SYSIBM	Returns quantities used to compute diagnostic statistics.
	numeric-type, numeric-type		DOUBLE
REPEAT	SYSFUN	Returns a character string composed of argument1 repeated argument2 times.
	VARCHAR(4000), INTEGER		VARCHAR(4000)
	CLOB(1M), INTEGER		CLOB(1M)
	BLOB(1M), INTEGER		BLOB(1M)
REPLACE	SYSFUN	Replaces all occurrences of argument2 in argument1 with argument3.
	VARCHAR(4000), VARCHAR(4000), VARCHAR(4000)		VARCHAR(4000)
	CLOB(1M), CLOB(1M), CLOB(1M)		CLOB(1M)
	BLOB(1M), BLOB(1M), BLOB(1M)		BLOB(1M)
RIGHT	SYSFUN	Returns a string consisting of the rightmost argument2 bytes in argument1.
	VARCHAR(4000), INTEGER		VARCHAR(4000)
	CLOB(1M), INTEGER		CLOB(1M)
	BLOB(1M), INTEGER		BLOB(1M)
ROUND	SYSFUN	Returns the first argument rounded to argument2 places right of the decimal point. If argument2 is negative, argument1 is rounded to the absolute value of argument2 places to the left of the decimal point.
	INTEGER, INTEGER		INTEGER
	BIGINT, INTEGER		BIGINT
	DOUBLE, INTEGER		DOUBLE
RTRIM	SYSIBM	Returns the characters of the argument with trailing blanks removed.
	CHAR		VARCHAR
	VARCHAR		VARCHAR
	GRAPHIC		VARGRAPHIC
	VARGRAPHIC		VARGRAPHIC
RTRIM	SYSFUN	Returns the characters of the argument with trailing blanks removed.
	VARCHAR(4000)		VARCHAR(4000)
	CLOB(1M)		CLOB(1M)
SECOND	SYSIBM	Returns the second (time-unit) part of a value.
	VARCHAR		INTEGER
	TIME		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
SIGN	SYSFUN	Returns an indicator of the sign of the argument. If the argument is less than zero, -1 is returned. If argument equals zero, 0 is returned. If argument is greater than zero, 1 is returned.
	SMALLINT		SMALLINT
	INTEGER		INTEGER
	BIGINT		BIGINT
	DOUBLE		DOUBLE
SIN	SYSFUN	Returns the sine of the argument, where the argument is an angle expressed in radians.
	DOUBLE		DOUBLE
SMALLINT	SYSIBM	Returns the small integer representation of a number.
	numeric-type		SMALLINT
	VARCHAR		SMALLINT
SOUNDEX	SYSFUN	Returns a 4 character code representing the sound of the words in the argument. The result can be used to compare with the sound of other strings. See also DIFFERENCE.
	VARCHAR(4000)		CHAR(4)
SPACE	SYSFUN	Returns a character string consisting of argument1 blanks.
	INTEGER		VARCHAR(4000)
SQLCACHE_SNAPSHOT	SYSFUN	Returns a table of the snapshot of the db2 dynamic SQL statement cache.
	Refer to SQLCACHE_SNAPSHOT.
SQRT	SYSFUN	Returns the square root of the argument.
	DOUBLE		DOUBLE
STDDEV	SYSIBM	Returns the standard deviation of a set of numbers (column function).
	DOUBLE		DOUBLE
SUBSTR	SYSIBM	Returns a substring of a string argument1 starting at argument2 for argument3 characters. If argument3 is not specified, the remainder of the string is assumed.
	string-type, INTEGER		string-type
	string-type, INTEGER, INTEGER		string-type
SUM	SYSIBM	Returns the sum of a set of numbers (column function).
	numeric-type 4		max-numeric-type 1
TABLE_NAME	SYSIBM	Returns an unqualified name of a table or view based on the object name given in argument1 and the optional schema name given in argument2. It is used to resolve aliases.
	VARCHAR		VARCHAR(128)
	VARCHAR, VARCHAR		VARCHAR(128)
TABLE_SCHEMA	SYSIBM	Returns the schema name portion of the two part table or view name given by the object name in argument1 and the optional schema name in argument2. It is used to resolve aliases.
	VARCHAR		VARCHAR(128)
	VARCHAR, VARCHAR		VARCHAR(128)
TAN	SYSFUN	Returns the tangent of the argument, where the argument is an angle expressed in radians.
	DOUBLE		DOUBLE
TIME	SYSIBM	Returns a time from a value.
	TIME		TIME
	TIMESTAMP		TIME
	VARCHAR		TIME
TIMESTAMP	SYSIBM	Returns a timestamp from a value or a pair of values.
	TIMESTAMP		TIMESTAMP
	VARCHAR		TIMESTAMP
	VARCHAR, VARCHAR		TIMESTAMP
	VARCHAR, TIME		TIMESTAMP
	DATE, VARCHAR		TIMESTAMP
	DATE, TIME		TIMESTAMP
TIMESTAMP_ISO	SYSFUN	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.
	DATE		TIMESTAMP
	TIME		TIMESTAMP
	TIMESTAMP		TIMESTAMP
	VARCHAR(26)		TIMESTAMP
TIMESTAMPDIFF	SYSFUN	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. Valid values of interval (argument1) are: 1 Fractions of a second 2 Seconds 4 Minutes 8 Hours 16 Days 32 Weeks 64 Months 128 Quarters 256 Years
	INTEGER, CHAR(22)		INTEGER
TRANSLATE	SYSIBM	Returns a string in which one or more characters may have been translated into other characters.
	CHAR		CHAR
	VARCHAR		VARCHAR
	CHAR, VARCHAR, VARCHAR		CHAR
	VARCHAR, VARCHAR, VARCHAR		VARCHAR
	CHAR, VARCHAR, VARCHAR, VARCHAR		CHAR
	VARCHAR, VARCHAR, VARCHAR, VARCHAR		VARCHAR
	GRAPHIC, VARGRAPHIC, VARGRAPHIC		GRAPHIC
	VARGRAPHIC, VARGRAPHIC, VARGRAPHIC		VARGRAPHIC
	GRAPHIC, VARGRAPHIC, VARGRAPHIC, VARGRAPHIC		GRAPHIC
	VARGRAPHIC, VARGRAPHIC, VARGRAPHIC, VARGRAPHIC		VARGRAPHIC
TRUNC or TRUNCATE	SYSFUN	Returns argument1 truncated to argument2 places right of the decimal point. If argument2 is negative, argument1 is truncated to the absolute value of argument2 places to the left of the decimal point.
	INTEGER, INTEGER		INTEGER
	BIGINT, INTEGER		BIGINT
	DOUBLE, INTEGER		DOUBLE
TYPE_ID 3	SYSIBM	Returns the internal data type identifier of the dynamic data type of the argument. Note that the result of this function is not portable across databases.
	any-structured-type		INTEGER
TYPE_NAME 3	SYSIBM	Returns the unqualified name of the dynamic data type of the argument.
	any-structured-type		VARCHAR(18)
TYPE_SCHEMA 3	SYSIBM	Returns the schema name of the dynamic type of the argument.
	any-structured-type		VARCHAR(128)
UCASE or UPPER	SYSIBM	Returns a string in which all the characters have been converted to upper case characters.
	CHAR		CHAR
	VARCHAR		VARCHAR
UCASE	SYSFUN	Returns a string in which all the characters have been converted to upper case characters.
	VARCHAR		VARCHAR
VALUE 3	SYSIBM	Same as COALESCE.
VARCHAR	SYSIBM	Returns a VARCHAR representation of the first argument. If a second argument is present, it specifies the length of the result.
	character-type		VARCHAR
	character-type, INTEGER		VARCHAR
	datetime-type		VARCHAR
VARGRAPHIC	SYSIBM	Returns a VARGRAPHIC representation of the first argument. If a second argument is present, it specifies the length of the result.
	graphic-type		VARGRAPHIC
	graphic-type, INTEGER		VARGRAPHIC
	VARCHAR		VARGRAPHIC
VARIANCE or VAR	SYSIBM	Returns the variance of a set of numbers (column function).
	DOUBLE		DOUBLE
WEEK	SYSFUN	Returns the week of the year in of the argument as an integer value in the range of 1-54.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
WEEK_ISO	SYSFUN	Returns the week of the year in of the argument as an integer value in the range of 1-53. The first day of a week is Monday. Week 1 is the first week of the year to contain a Thursday.
	VARCHAR(26)		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
YEAR	SYSIBM	Returns the year part of a value.
	VARCHAR		INTEGER
	DATE		INTEGER
	TIMESTAMP		INTEGER
	DECIMAL		INTEGER
"+"	SYSIBM	Adds two numeric operands.
	numeric-type, numeric-type		max numeric-type
"+"	SYSIBM	Unary plus operator.
	numeric-type		numeric-type
"+"	SYSIBM	Datetime plus operator.
	DATE, DECIMAL(8,0)		DATE
	TIME, DECIMAL(6,0)		TIME
	TIMESTAMP, DECIMAL(20,6)		TIMESTAMP
	DECIMAL(8,0), DATE		DATE
	DECIMAL(6,0), TIME		TIME
	DECIMAL(20,6), TIMESTAMP		TIMESTAMP
	datetime-type, DOUBLE, labeled-duration-code		datetime-type
"-"	SYSIBM	Subtracts two numeric operands.
	numeric-type, numeric-type		max numeric-type
"-"	SYSIBM	Unary minus operator.
	numeric-type		numeric-type 1
"-"	SYSIBM	Datetime minus operator.
	DATE, DATE		DECIMAL(8,0)
	TIME, TIME		DECIMAL(6,0)
	TIMESTAMP, TIMESTAMP		DECIMAL(20,6)
	DATE, VARCHAR		DECIMAL(8,0)
	TIME, VARCHAR		DECIMAL(6,0)
	TIMESTAMP, VARCHAR		DECIMAL(20,6)
	VARCHAR, DATE		DECIMAL(8,0)
	VARCHAR, TIME		DECIMAL(6,0)
	VARCHAR, TIMESTAMP		DECIMAL(20,6)
	DATE, DECIMAL(8,0)		DATE
	TIME, DECIMAL(6,0)		TIME
	TIMESTAMP, DECIMAL(20,6)		TIMESTAMP
	datetime-type, DOUBLE, labeled-duration-code		datetime-type
"*"	SYSIBM	Multiplies two numeric operands.
	numeric-type, numeric-type		max numeric-type
"/"	SYSIBM	Divides two numeric operands.
	numeric-type, numeric-type		max numeric-type
"||"	SYSIBM	Same as CONCAT.

Notes References to string data types that are not qualified by a length should be assumed to support the maximum length for the data type References to a DECIMAL data type without precision and scale should be assumed to allow any supported precision and scale.

Key to Table any-builtin-type Any data type that is not a distinct type. any-type Any type defined to the database. any-structured-type Any user-defined structured type defined to the database. any-comparable-type Any type that is comparable with other argument types as defined in Assignments and Comparisons. any-union-compatible-type Any type that is compatible with other argument types as defined in Rules for Result Data Types. character-type Any of the character string types: CHAR, VARCHAR, LONG VARCHAR, CLOB. compatible-string-type A string type that comes from the same grouping as the other argument (e.g. if one argument is a character-type the other must also be a character-type). datetime-type Any of the datetime types: DATE, TIME, TIMESTAMP. graphic-type Any of the double byte character string types: GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, DBCLOB. labeled-duration-code As a type this is a SMALLINT. If the function is invoked using the infix form of the plus or minus operator, labeled-durations as defined in Labeled Durations can be used. For a source function that does not use the plus or minus operator character as the name, the following values must be used for the labeled-duration-code argument when invoking the function. 1 YEAR or YEARS 2 MONTH or MONTHS 3 DAY or DAYS 4 HOUR or HOURS 5 MINUTE or MINUTES 6 SECOND or SECONDS 7 MICROSECOND or MICROSECONDS LOB-type Any of the large object types: BLOB, CLOB, DBCLOB. max-numeric-type The maximum numeric type of the arguments where maximum is defined as the rightmost numeric-type. max-string-type The maximum string type of the arguments where maximum is defined as the rightmost character-type or graphic-type. If arguments are BLOB, the max-string-type is BLOB. numeric-type Any of the numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE. string-type Any type from character type, graphic-type or BLOB.

Table Footnotes 1 When the input parameter is SMALLINT, the result type is INTEGER. When the input parameter is REAL, the result type is DOUBLE. 2 Keywords allowed are ISO, USA, EUR, JIS, and LOCAL. This function signature is not supported as a sourced function. 3 This function cannot be used as a source function. 4 The keyword ALL or DISTINCT may be used before the first parameter. If DISTINCT is specified, the use of user-defined structured types, long string types or a DATALINK type is not supported. 5 The use of user-defined structured types, long string types or a DATALINK type is not supported. 6 The type returned by RAISE_ERROR depends upon the context of its use. RAISE_ERROR, if not cast to a particular type, will return a type appropriate to its invocation within a CASE expression.