DB2-SQL数据库函数

DB2-SQL 数据库函数1、 ABS(exp) 取绝对值52、 ABSVAL(exp) 取绝对值53、 ACOS(exp) 取反余弦54、 ASCII(exp) 取最左第一个字符ASCII码55、 ASIN(exp) 反正弦函数56、 ATAN(exp) 反正切函数57、 ATAN2(x,y) y/x的反正切函数58、 AVG(exp) 平均数59、 CEIL(exp) 向上取整数,即取最大整数值510、 CEILING(exp) 向上取整数,同上511、 CHAR(exp,format)转换字符型 format-取字符左起长度数512、 CHR(char1) 返回单字符的ASCII码513、 CONCAT(exp1,exp2)连接两个字符串，同|514、 COS(exp) 余弦函数515、 COT(exp) 余切函数516、 DAY(date exp) 返回日期中的dd517、 DAYNAME(date exp)转换日期中的英文星期518、 DAYOFWEEK(date exp)返回日期中的本周星期数1-7519、 DAYOFYEAR(date exp)返回日期中从本年年初到该日的天数1-366520、 DEGREES(exp) 返回弧度521、 DIFFERENCE(exp,exp)比较不同之处与SOUNDEX()使用?522、 DOUBLE(exp) 转换为double型523、 EXP(exp) 指数函数524、 FLOOR(exp) 向下取整数,即取最小整数值525、 INSERT(char exp1,exp2,exp3,char exp4)插入526、 JULIAN_DAY(exp)从？年一月开始到该日期的天数627、 LEFT(exp1,exp2) 返回exp2在exp1的最左出现的位子628、 LOG(exp) 取自然对数629、 LOG10(exp) 取对数630、 LTRIM(exp) 左去空格631、 LCASE(exp) 返回小写字符串632、 LN(exp) ln(x)633、 LOCATE(exp1,exp2,exp3)查找exp1位于exp2从第exp3位开始634、 MIDNIGHT_SECONDS(exp)返回当日0点到该时间的秒数635、 MOD(exp) 求模636、 MONTHNAME(exp)月的英文名637、 POWER(exp1,exp2)求幂638、 QUARTER(exp) 返回日期的季度数，范围1-4639、 RADIANS(exp) 返回弧度640、 RAND(exp) 根据exp返回随机数，范围0-1641、 REPEAT(exp1,exp2)重复exp1,exp2次642、 REPLACE(exp1,exp2,exp3)将exp1中出现的所有exp2字符替换成exp3643、 RIGHT(exp1,exp2) 返回exp2在exp1最右边出现的位子644、 ROUND(exp1,exp2)四舍五入645、 RTRIM(exp) 右边去空格646、 SECOND(exp) 返回时间的秒数647、 SIGN(exp) 标记 exp0 return 1748、 SIN(exp) 正弦函数749、 SMALLINT(exp) 返回整数字范围-32769t0 到小数点后exp2位758、 TRUNCATE(exp1,exp2)截断(同TRUNC)759、 UCASE(exp) 转换为大写760、 VEBLOB_CP_LARGE强制大写761、 VEBLOB_CP_SMALL强制小写762、 WEEK(exp) 返回该日是本年的第几个星期7附表：FUNCTION81、 ABS(exp)取绝对值2、 ABSVAL(exp)取绝对值3、 ACOS(exp)取反余弦4、 ASCII(exp)取最左第一个字符ASCII码5、 ASIN(exp)反正弦函数6、 ATAN(exp)反正切函数7、 ATAN2(x,y)y/x的反正切函数8、 AVG(exp)平均数9、 CEIL(exp)向上取整数,即取最大整数值10、 CEILING(exp)向上取整数,同上11、 CHAR(exp,format)转换字符型 format-取字符左起长度数 Format = iso 可以转换日期格式12、 CHR(char1)返回单字符的ASCII码13、 CONCAT(exp1,exp2)连接两个字符串，同|14、 COS(exp)余弦函数15、 COT(exp)余切函数16、 DAY(date exp)返回日期中的dd17、 DAYNAME(date exp)转换日期中的英文星期18、 DAYOFWEEK(date exp)返回日期中的本周星期数1-719、 DAYOFYEAR(date exp)返回日期中从本年年初到该日的天数1-36620、 DEGREES(exp)返回弧度21、 DIFFERENCE(exp,exp)比较不同之处与SOUNDEX()使用?22、 DOUBLE(exp)转换为double型23、 EXP(exp)指数函数24、 FLOOR(exp)向下取整数,即取最小整数值25、 INSERT(char exp1,exp2,exp3,char exp4)插入将exp4插入到exp1中，从第exp2位开始插入，插入覆盖exp1的长度exp3例如：insert( abcde,1,0,!)=!abcde insert( abcde,2,2,!)=a!de insert( acbde,2,3,!)=a!e26、 JULIAN_DAY(exp)从？年一月开始到该日期的天数27、 LEFT(exp1,exp2)返回exp2在exp1的最左出现的位子28、 LOG(exp)取自然对数29、 LOG10(exp)取对数30、 LTRIM(exp)左去空格31、 LCASE(exp)返回小写字符串32、 LN(exp)ln(x)33、 LOCATE(exp1,exp2,exp3)查找exp1位于exp2从第exp3位开始 出现的位数34、 MIDNIGHT_SECONDS(exp)返回当日0点到该时间的秒数Exp=hh-mm-ss35、 MOD(exp)求模36、 MONTHNAME(exp)月的英文名37、 POWER(exp1,exp2)求幂38、 QUARTER(exp)返回日期的季度数，范围1-439、 RADIANS(exp)返回弧度40、 RAND(exp)根据exp返回随机数，范围0-1exp相同产生的随机数亦相同41、 REPEAT(exp1,exp2)重复exp1,exp2次42、 REPLACE(exp1,exp2,exp3)将exp1中出现的所有exp2字符替换成exp343、 RIGHT(exp1,exp2)返回exp2在exp1最右边出现的位子44、 ROUND(exp1,exp2)四舍五入Exp2=n 四舍五入后保留到小数点后n位Round( 99.12345,1)=99.1Exp2=0 不进行四舍五入Exp2= -n 四舍五入后保留到小数点前n位Round( 99876.4555,-2)=9988045、 RTRIM(exp)右边去空格46、 SECOND(exp)返回时间的秒数47、 SIGN(exp)标记 exp0 return 1 exp=0 return 0 expt32768exp 还可以是数字型字符串或数字但范围在exp -32769否则报错50、 SOUNDEX(char exp)一种探测方法，返回四位的字符?规律不明51、 SPACE(exp)返回exp个空格Db2 “values char( space(2) | abcd )”52、 SQLCACHE_SNAPSHOT53、 SQRT(exp)平方根54、 TAN(exp)正弦函数55、 TIMESTAMP(exp)返回时间戳56、 TIMESTAMPDIFF(exp)返回时间戳57、 TRUNC(exp1,exp2)截断exp20 到小数点后exp2位 Exp20 到小数点前 exp2位58、 TRUNCATE(exp1,exp2)截断(同TRUNC)59、 UCASE(exp)转换为大写60、 VEBLOB_CP_LARGE强制大写61、 VEBLOB_CP_SMALL强制小写62、 WEEK(exp)返回该日是本年的第几个星期附表：FUNCTIONFunction 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 86400 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