db2函数-v2

WEEK_ISO(expression) TO_CHAR(timestampexpression,,format-string) WEEK(expression)
MOD(expression,expression) TRANSLATE(stringexp,tostring,fromst ring) 实际中经常如下使用： translate(exp,'','0123456789') replace(source-string, pattern, replacement-string) dayofweek_iso(exp)
TRUNC(double sourceNum, int place)
1 1
简单转换：round(double a, int d) 注：trunc与round的区别是：trunc无四舍五入 substr(string A, length(string A)-len+1, int len) 无 无 无 无 无 无 无 无 无 无 无 无
sin(double a) MIDNIGHT_SECONDS
0 rpad(string str, int len, string pad) 0 0 0 0 repeat(string str, int n) 0 cast(… as <obj>) 0 rand(),rand(int seed) 0 0 radians(double a) 0 0 0 power(double a, double p) 0 0 0 0 nvl 0
0无 0无 0无 0无 0无 0无 0无 0 covar_samp(col1, col2) 0 cast(… as <obj>) cast(… as <obj>) + 0 from_unixtime 0 0 0 sustr 0 +cast(… as <obj>) 0 0 0 cast(… as <obj>) 0 TO_DATE(v_txdate,'YYYYMMDD') 0 date_add(string startdate, int days) 0 0 unix_timestamp(string date, string pattern) 0 0 tan(double a) 0 substring(string A, int start, int len) 0 0 var_pop(col) 0 0 0 0 0 0 0 0 0 0 sqrt(double a) space(int n)
DEC(ARG)
DAYS(expression) STRIP(stringExpression,flag,,stripC haracter) flag取值： B：BOTH表示两头 L：LEADING表示去头 T：TRAILING表示去尾 sysdate TIMESTAMP(EXPRES1) TIMESTAMP(EXPRES1[,EXPRES2]) (很少 使用，暂不考虑) TIME(expression)
RIGHT(ARG,LENGTH)
1 0 0 0 0 0 0 0 0 0 0 0 0
XMLCOMMENT XMLATTRIBUTES XMLAGG VERIFY_TRUSTED_CON TEXT_ROLE_FOR_USER VERIFY_ROLE_FOR_US ER VERIFY_GROUP_FOR_U SER VARIANCE_SAMP VARIANCE VARGRAPHIC VARCHAR_FORMAT VARBINARY UNICODE_STR UNICODE TRUNCATE TRUNC_TIMESTAMP TOTALORDER TO_NUMBER TO_DATE TIMESTAMPADD TIMESTAMP_TZ TIMESTAMP_FORMAT TANH TAN SUBSTRING STDDEV_SAMP STDDEV SQRT SPACE SOUNDEX SOAPHTTPV SOAPHTTPNV SOAPHTTPNC SOAPHTTPC SINH SIN SECOND RPAD ROWID ROUND_TIMESTAMP RID REPEAT REAL RAND RAISE_ERROR RADIANS QUARTER QUANTIZE POWER POSSTR POSITION PACK OVERLAY NVL
db2中用法 VALUE(EXPRESSION1,EXPRESSION2) CHAR(ARG) DECIMAL(ARG) VARCHAR(ARG) VARCHAR(ARG,LENGTH) DATE(ARG)
次数 22256 18398 4010 3983lesce(T v1, T v2) count cast(… as string) 或 string() cast(… as decimal) string(arg) 2706 substr(string(arg), 0,int len) 2404 to_date(string timestamp) 1502 max 998 substr(string A, int start, int len) 689 cast(… as int) 或 int() 515 sign(double a) 427 411 373 354 311 259 231 209 188 179 161 161 139 136 129 115 trim(string A) round(double a) rtrim(string A) cast(… as decimal) month(string date) day(string date) dayofmonth(date) cast(… as double) 或 double() upper(string A) ucase(string A) avg dayofmonth(date) min max cast(… as <obj>) 或 bigint() length(string A) ltrim(string A) year datediff(string enddate, string "0001-0163 01") 删除空白字符：rtrim(string A),ltrim(string A),trim(string A) 删除空白或指定字符：regexp_replace(string A, string B, "") 56 其中参数B根据db2中的flag转换： flag=B：stripCharacter*$|^stripCharacter* flag=L：^stripCharacter* flag=T：stripCharacter*$ from_unixtime(unix_timestamp(), "yyyy-MM-dd hh:mm:ss") 54 cast(… as <obj>) from_unixtime(unix_timestamp(exp),'hh:mm:ss ') 47 cast(… as <obj>) 或 smallint() 42 hour(string date) 36 lower(string A) lcase(string A) 53
LEFT(ARG,LENGTH)
简单替换：double(exp2) / $base 更优替换：(unix_timestamp(enddate)unix_timestamp(startdate)) / $base 其他(间隔天或比天大的单位)替换： datediff(enddate, startdate) 注：when exp1=1 then base=0.001 when exp1=1 then base=0.001 34 when exp1=2 then base=1 when exp1=4 then base=60 when exp1=8 then base=(60 * 60) when exp1=16 then base=(60 * 60 * 24) when exp1=32 then base=(60 * 60 * 24 * 7) when exp1=64 then base=(60 * 60 * 24 * 30) when exp1=128 then base=(60 * 60 * 24 * 30 * 3) 29 cast(… as <obj>) 25 20 ceiling(2345.67) ? locate(string substr, string str[, int 19 pos]) 18 substr(string A, 0, int len) 17 abs(double a) 15 minute(string date) 14 MIDNIGHT_SECONDS hex(BIGINT a) 12 hex(string a) 11 weekofyear(string date) from_unixtime(unix_timestamp(exp)[, string 11 format]) weekofyear(string date)（注:weekofyear不完 10 全符合DB2中的week函数） cast(… as <obj>) 或bigint() 或 int()（注： 10 db2中的integer在转换时间时与hive中不同） 9 pmod(int a, int b) pmod(double a, double b) 类似于regexp_replace，但不相同 regexp_replace(string A, [fromstring], 5 tostring) 注：若tostring为多个字符，则此转换无效 4 ucase(string A) regexp_replace(string A, string B, string 4 C) 注：DB2中replace的某些特殊用法，在 4 exp(double a) 4 pmod(datediff(exp,'1900-01-01'),7)+1 3 concat(string A, string B…)