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.

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.


[ Top of Page | Previous Page | Next Page ]