db2函数积累

一. 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'