DB2-SQL數(shù)據(jù)庫函數(shù)

DB2-SQL 數(shù)據(jù)庫函數(shù)1、 ABS(exp) 取絕對值52、 ABSVAL(exp) 取絕對值53、 ACOS(exp) 取反余弦54、 ASCII(exp) 取最左第一個字符ASCII碼55、 ASIN(exp) 反正弦函數(shù)56、 ATAN(exp) 反正切函數(shù)57、 ATAN2(x,y) y/x的反正切函數(shù)58、 AVG(exp) 平均數(shù)59、 CEIL(exp) 向上取整數(shù),即取最大整數(shù)值510、 CEILING(exp) 向上取整數(shù),同上511、 CHAR(exp,format)轉(zhuǎn)換字符型 format-取字符左起長度數(shù)512、 CHR(char1) 返回單字符的ASCII碼513、 CONCAT(exp1,exp2)連接兩個字符串，同|514、 COS(exp) 余弦函數(shù)515、 COT(exp) 余切函數(shù)516、 DAY(date exp) 返回日期中的dd517、 DAYNAME(date exp)轉(zhuǎn)換日期中的英文星期518、 DAYOFWEEK(date exp)返回日期中的本周星期數(shù)1-7519、 DAYOFYEAR(date exp)返回日期中從本年年初到該日的天數(shù)1-366520、 DEGREES(exp) 返回弧度521、 DIFFERENCE(exp,exp)比較不同之處與SOUNDEX()使用?522、 DOUBLE(exp) 轉(zhuǎn)換為double型523、 EXP(exp) 指數(shù)函數(shù)524、 FLOOR(exp) 向下取整數(shù),即取最小整數(shù)值525、 INSERT(char exp1,exp2,exp3,char exp4)插入526、 JULIAN_DAY(exp)從？年一月開始到該日期的天數(shù)627、 LEFT(exp1,exp2) 返回exp2在exp1的最左出現(xiàn)的位子628、 LOG(exp) 取自然對數(shù)629、 LOG10(exp) 取對數(shù)630、 LTRIM(exp) 左去空格631、 LCASE(exp) 返回小寫字符串632、 LN(exp) ln(x)633、 LOCATE(exp1,exp2,exp3)查找exp1位于exp2從第exp3位開始634、 MIDNIGHT_SECONDS(exp)返回當(dāng)日0點到該時間的秒數(shù)635、 MOD(exp) 求模636、 MONTHNAME(exp)月的英文名637、 POWER(exp1,exp2)求冪638、 QUARTER(exp) 返回日期的季度數(shù)，范圍1-4639、 RADIANS(exp) 返回弧度640、 RAND(exp) 根據(jù)exp返回隨機數(shù)，范圍0-1641、 REPEAT(exp1,exp2)重復(fù)exp1,exp2次642、 REPLACE(exp1,exp2,exp3)將exp1中出現(xiàn)的所有exp2字符替換成exp3643、 RIGHT(exp1,exp2) 返回exp2在exp1最右邊出現(xiàn)的位子644、 ROUND(exp1,exp2)四舍五入645、 RTRIM(exp) 右邊去空格646、 SECOND(exp) 返回時間的秒數(shù)647、 SIGN(exp) 標記 exp0 return 1748、 SIN(exp) 正弦函數(shù)749、 SMALLINT(exp) 返回整數(shù)字范圍-32769t0 到小數(shù)點后exp2位758、 TRUNCATE(exp1,exp2)截斷(同TRUNC)759、 UCASE(exp) 轉(zhuǎn)換為大寫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)反正弦函數(shù)6、 ATAN(exp)反正切函數(shù)7、 ATAN2(x,y)y/x的反正切函數(shù)8、 AVG(exp)平均數(shù)9、 CEIL(exp)向上取整數(shù),即取最大整數(shù)值10、 CEILING(exp)向上取整數(shù),同上11、 CHAR(exp,format)轉(zhuǎn)換字符型 format-取字符左起長度數(shù) Format = iso 可以轉(zhuǎn)換日期格式12、 CHR(char1)返回單字符的ASCII碼13、 CONCAT(exp1,exp2)連接兩個字符串，同|14、 COS(exp)余弦函數(shù)15、 COT(exp)余切函數(shù)16、 DAY(date exp)返回日期中的dd17、 DAYNAME(date exp)轉(zhuǎn)換日期中的英文星期18、 DAYOFWEEK(date exp)返回日期中的本周星期數(shù)1-719、 DAYOFYEAR(date exp)返回日期中從本年年初到該日的天數(shù)1-36620、 DEGREES(exp)返回弧度21、 DIFFERENCE(exp,exp)比較不同之處與SOUNDEX()使用?22、 DOUBLE(exp)轉(zhuǎn)換為double型23、 EXP(exp)指數(shù)函數(shù)24、 FLOOR(exp)向下取整數(shù),即取最小整數(shù)值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)從？年一月開始到該日期的天數(shù)27、 LEFT(exp1,exp2)返回exp2在exp1的最左出現(xiàn)的位子28、 LOG(exp)取自然對數(shù)29、 LOG10(exp)取對數(shù)30、 LTRIM(exp)左去空格31、 LCASE(exp)返回小寫字符串32、 LN(exp)ln(x)33、 LOCATE(exp1,exp2,exp3)查找exp1位于exp2從第exp3位開始 出現(xiàn)的位數(shù)34、 MIDNIGHT_SECONDS(exp)返回當(dāng)日0點到該時間的秒數(shù)Exp=hh-mm-ss35、 MOD(exp)求模36、 MONTHNAME(exp)月的英文名37、 POWER(exp1,exp2)求冪38、 QUARTER(exp)返回日期的季度數(shù)，范圍1-439、 RADIANS(exp)返回弧度40、 RAND(exp)根據(jù)exp返回隨機數(shù)，范圍0-1exp相同產(chǎn)生的隨機數(shù)亦相同41、 REPEAT(exp1,exp2)重復(fù)exp1,exp2次42、 REPLACE(exp1,exp2,exp3)將exp1中出現(xiàn)的所有exp2字符替換成exp343、 RIGHT(exp1,exp2)返回exp2在exp1最右邊出現(xiàn)的位子44、 ROUND(exp1,exp2)四舍五入Exp2=n 四舍五入后保留到小數(shù)點后n位Round( 99.12345,1)=99.1Exp2=0 不進行四舍五入Exp2= -n 四舍五入后保留到小數(shù)點前n位Round( 99876.4555,-2)=9988045、 RTRIM(exp)右邊去空格46、 SECOND(exp)返回時間的秒數(shù)47、 SIGN(exp)標記 exp0 return 1 exp=0 return 0 expt32768exp 還可以是數(shù)字型字符串或數(shù)字但范圍在exp -32769否則報錯50、 SOUNDEX(char exp)一種探測方法，返回四位的字符?規(guī)律不明51、 SPACE(exp)返回exp個空格Db2 “values char( space(2) | abcd )”52、 SQLCACHE_SNAPSHOT53、 SQRT(exp)平方根54、 TAN(exp)正弦函數(shù)55、 TIMESTAMP(exp)返回時間戳56、 TIMESTAMPDIFF(exp)返回時間戳57、 TRUNC(exp1,exp2)截斷exp20 到小數(shù)點后exp2位 Exp20 到小數(shù)點前 exp2位58、 TRUNCATE(exp1,exp2)截斷(同TRUNC)59、 UCASE(exp)轉(zhuǎn)換為大寫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