一. coalesce
语法:
COALESCE ( expression [ ,...n ] )
参数
expression
任何类型的表达式。
n
表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。(否则函数报错)
返回类型
将第一个非空值的相同值作为 expression 返回。
例子:
select coalesce(null,null,3,'4',5) from sysibm.sysdummy1; ----结果为: 3
select coalesce(DEPART,'UNKNOW') from EMP; ---emp表中的depart列为字符型,此语句的意义为:从emp表中选出DEPART列,如果为空则显示为UNKNOW.
select coalesce(salary,0) from emp; ---从emp表中选出员工的工资,如果为空则显示0.
类似的db2支持的函数还有
---NVL(expression1,expression2) 同样返回2个表达式中的非空值。
---VALUE(EXPRESSION1,EXPRESSION2) 同样返回2个表达式中的非空值。
二.decimal(express,length,sacle) ---DEC
将数字型的表达式取scale位小数。如:
select decimal(456.3,5,2) from sysibm.sysdummy1; ---456.30
select decimal(456.3245,6,3) from sysibm.sysdummy1; ---456.324 取3位小数(直接截断)
select decimal(456.3245 + 0.0005,6,3) from sysibm.sysdummy1; ---456.325 取3位小数(4舍5入),3位要加0.0005,2位加0.005,以此类推。
DEC(VALUES1,15,2) = cast(VALUES1 as decimal(15,2))
三.int(express)
取数字型表达式的整数位。相当于decimal(express,length,0)
select int(12.456) from sysibm.sysdummy1; ---12 等价于:select decimal(12.456,2,0) from sysibm.sysdummy1; ----12
四.substr(express,start,length)
截取字符串,从start开始,取length位长度。
举例:
select substr('12345',1,2) from sysibm.sysdummy1; -----12
db2还提供了从左取字符串的函数left和从右取字符串的函数right
举例如下:
select left('aaaaabbb',2),substr('aaaaabbb',1,2) from sysibm.sysdummy1; ----aa ,aa 即substr('aaaaabbb',1,2) = left('aaaaabbb',2)
select right('aaaaddd',4) from sysibm.sysdummy1; ---addd
请注意:
substr还有一种用法为substr(m,n)表示对字符串m从第n位开始截取直到结尾。
如下:
select substr('who are you?',5) from sysibm.sysdummy1; ----'are you?'
五.cast(N AS TYPE)
将参数N转换为TYPE型。
比如
select cast(1 as float)/3 from sysibm.sysdummy1; ---将1转换为float型,然后除以3.
select cast(1 as decimal(10,2))/3 from sysibm.sysdummy1; --将1转换为decimal(10,2)型,然后除以3.
select cast(1111 as char(4)) from sysibm.sysdummy1; --将1111转换为字符型的'1111',要注意的是不可以转换为varchar,这是db2不允许的。
六.length(expression)
求expression的长度(所占字节数)
如:
select length(1) from sysibm.sysdummy1; ---4
七.ABS(绝对值函数)
八.UPPER()和LOWER()
大小写转换函数,UPPER()为全部转换为大写,LOWER()为全部转换为小写。
九.POWER和sqrt
power(a,b) ---求a的b次方。power(2,3)=8
sqrt平方根函数。 sqrt(4)=2
十.随机数
DB2 没有内置的生成随机数的函数,不过DB2 的SYSFUN 包提供了rand函数用来生成随机数,在使用之前要确保已经被正确安装
db2: sysfun.rand()
select sysfun.rand() from sysibm.sysdummy1;
ORALCE: dbms_random()
select dbms_random.value() from dual; ---生成随机数
select dbms_random.value(50,80) from dual; ---生成50到80之间的随机数
十一.向上舍入到最大的整数、向下舍入到最大整数
向上舍入到最大的整数
CEILING(db2): CEIL(ORACLE)
SELECT CEILING(33.33) FROM SYSIBM.SYSDUMMY1; 34
SELECT CEILING(-33.33) FROM SYSIBM.SYSDUMMY1; -33
向下舍入到最大整数
FLOOR(DB2,ORACLE)
SELECT FLOOR(33.33) FROM SYSIBM.SYSDUMMY1; 33
SELECT FLOOR(-33.33) FROM SYSIBM.SYSDUMMY1; -34
十二.round(m,n)
n>=0 对m四舍五入取n为小数 比如
select round(33.338,1) from sysibm.sysdummy1; --33.3
select round(33.338,2) from sysibm.sysdummy1; --33.34
select round(33.338,0),round(33.338) from sysibm.sysdummy1; --33,33 round(33.338,0)=round(33.338)
n<0的时候,表示在整数部分进行四舍五入 比如:
select round(33.338,-1) from sysibm.sysdummy1; --30
十三.三角函数
sin、cos、tan ---正弦、余弦、正切
asin、acos、atan --反正弦、反余弦、反正切
十四. π值
select acos(-1) from sysibm.sysdummy1; --因为-1的反余弦值是π,所以π等于acos(-1)
十五. sign() 求数字的符号
select sign(33),sign(-3),sign(0) from sysibm.sysdummy1; ----得到 1,-1, 0
十六.mod(m,n) --m对n求模运算。
select mod(1,3) from sysibm.sysdummy1; --1
十七.对数函数
db2: log(m) 求以自然对数e为底m的对数。 select log(11) from sysibm.sysdummy1;
oracle: ln(m) 求以自然对数e为底m的对数。 select ln(11) from dual;
db2: log10(m) 求以10为底m的对数。
oracle: log(m,n) 求以m为底n的对数。 select log(2,16) from dual; ---4
十八. instr(源字符串, 目标字符串, 起始位置, 匹配序号)
函数返回“目标字符串”从“起始位置”开始在“源字符串”中第“匹配序号”次出现时的位置。
如下例:
select instr('englishli','li',5,1) from sysibm.sysdummy1; --8
select instr('englishli','li',1,1) from sysibm.sysdummy1; --4
select instr('englishli','li',1,2) from sysibm.sysdummy1; --8
此函数在db2和oracle中通用的。
还有一个db2中的函数locate。
locate(str1,str2,m) 找到在str2中str1第m次出现的位置。(这个函数和instr的顺序相反,要注意)
十九.字符串替换函数 replace(目标字符串,想要替换的字符串,替换的新内容)
例如:
select replace('aabbccdd','a','e') from sysibm.sysdummy1; --eebbccdd
oracle和db2中都有此函数
二十.求一个字符的ASCII码
db2和oracle一致:
select ASCII('A'),ASCII('a') from sysibm.sysdummy1; --65,97
二十一.查看一个ASCII码代表的是什么字符。
db2和oracle一致:
这个和二十刚好相反。
select chr(97),chr(65) from sysibm.sysdummy1; ---'a','A'