WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606
全部博文(599)
分类: DB2/Informix
2012-08-16 15:12:47
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: | |
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. | |||
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. | |
| |||
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. |