DB2函数——常用函数整理

一、判断函数

VALUE函数

语法：VALUE(EXPRESSION1,EXPRESSION2)
VALUE函数是用返回一个非空的值，当其第一个参数非空，直接返回该参数的值，如果第一个参数为空，则返回第二个参数的值。

--表示如果T1.ID为空，则返回空串，如果T1.ID不为空，则返回T1.ID。
SELECT VALUE(ID,'') FROM T1

COALESCE函数

语法：COALESCE(ARG1,ARG2...)
COALESCE返回参数集中第一个非null参数。用法类似于VALUE函数。当其第一个参数非空，直接返回该参数的值，如果第一个参数为空，则返回第二个参数的值
NVL函数
NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以就有了NVL2函数。
拓展：NVL2函数:Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

case when 函数

select      ID,Username,namer=(case when(score<='50')then '实习'

when(score>'50'  and  score<='500' )   then '赤脚医生'   

when(score>'500'  and score<='1000' )   then '村卫生员' 

when(score>'1000'  and score<='1500' )   then '乡卫生员' 

when(score>'1500'  and score<='2000' )   then '镇卫生员'

when(score>'2000'  and score<='3000' )   then '医师'

when(score>'3000'  and score<='5000' )   then '主治医师'

when(score>'5000'  and score<='10000' )   then '副主任医师'

when(score>'10000'  and score<='20000' )   then '主任医师'

when(score>'20000'  and score<='50000' )   then '健康大使'

else   '健康大使'  end ), (SELECT count(id)

FROM  jk01_YiWen_Question 

WHERE  UserID =  dbo.jk01_Member.ID)  as  questionnum 

from  jk01_Member

exists(A)函数

判断是否存在，存在数据返回true，不存在返回false

先记住怎么用，不懂

1、不关联情况下，内表有数据，用not exists查不到

不关联不带入去检验，判断里面是否查询出来返回为true或者false

2、关联情况下，内表有数据，not exists查询的是外表不存在于内表的数据

将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

这里的存在是存在于，举个例子来说 a表中有两条数据

1、判断子表中存在数据么，存在的话返回true，即where语句执行

select * from A a where  exists (select 1 from A b )

执行结果如下

2、判断子表中不存在数据么，不存在的话返回true，即where语句执行

select * from A a where not exists (select 1 from A b )

执行结果如下

 

3、如果在子表中加入条件使其关联外表

select * from A a where  exists (select 1 from A b where b.a=a.a )

执行结果如下

4、如果在子表的查询语句中加上where条件过滤并且与外表关联

select * from A a where  exists (select 1 from A b where b.a=a.a and b.a='1a' )

5、如果在子表的查询语句中加上where条件过滤并且与外表关联

select * from A a where not exists (select 1 from A b where b.a=a.a and b.a='1a' )

关联上表的时候就可以理解为与In一个用法，判断外表存在于内表里的数据或者不存在于内表里的数据并且显示出来

二、字符串函数

判断字符在字符串中的位置
1、instr函数

语法:instr('aa|bb|cc|dd|','|',1,1) aa   --多了一个结束标记位置

instr(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)//返回找到的位置，如果找不到则返回

ps:数据库中字符串索引都是从1开始，从0开始的话是搜索不到的，例如:select instr('oracle traning','ra',0,2) instring from dual;索引为0返回的值是0

ps:默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。


2、LOCATE函数 
语法：LOCATE(ARG1,ARG2,<POS>)  --只有起始位置
LOCATE函数在ARG2中查找ARG1第一次出现的位置，如果指定POS，则从ARG2的POS处开始查找ARG1第一次出现的位置。

SELECT LOCATE('a',NAME) FROM T1

3、POSSTR函数 语法：POSSTR(EXP1,EXP2) POSSTR函数返回EXP2在EXP1中的位置。 --没有起始位置和结束位置 SELECT LOCATE(NAME，'a') FROM T1

返回字符串长度
2、LENGTH函数
语法：LENGTH(ARG)
LENGTH函数返回参数的长度。

eg:
SELECT LENGTH(NAME) FROM T1

字符串变大小写
1、LCASE、LOWER函数
语法：LCASE()、LOWER()
LCASE、LOWER函数返回定长、变长字符串的小写形式。

eg:
SELECT LCASE(NAME),LOWER(NAME) FROM T1


2、UCASE、UPPER函数
语法：UCASE()、UPPER()
UCASE、UPPER函数返回定长、变长字符串的大写形式。

SELECT UCASE(NAME),UPPER(NAME) FROM T1

去除空格
1、LTRIM、RTRIM函数

DB2中无trim（）函数，所以要去空格要执行左右去空格。
语法：LTRIM()、RTRIM()
LTRIM、RTRIM函数从CHAR、VARCHAR、GRAPHIC或者VARGRAPHIC中去掉左侧或右侧的空格。

eg:
SELECT LTRIM(NAME),RTRIM(NAME) FROM T1


2、LEFT、RIGHT函数
语法：LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)
LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。

eg:
SELECT LEFT(NAME,2),RIGHT(NAME,2) FROM T1

连接字符串
1、CONCAT函数
语法：CONCAT(ARG1,ARG2)
CONCAT函数返回两个字符串的连接。

eg:
SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM T1

替换字符串
1、INSERT函数
语法：INSERT(ARG1,POS,SIZE,ARG2)   --找到位置替换
INSERT函数返回一个字符串，将ARG1从POS处删除SIZE个字符，将ARG2插入该位置。

2、REPLACE函数
语法：REPLACE(EXP1,EXP2,EXP3)
REPLACE函数用EXP3代替EXP1中所有的EXP2。

eg:
SELECT REPLACE('ROMANND'，'NND','CCB') FROM T1

根据条件查找字符串，返回字符串
1、REPEAT函数
语法：REPEAT(ARG1,NUM_TIMES)
REPEAT函数返回ARG1被重复NUM_TIMES次的字符串。

eg:
SELECT REPEAT(NAME，2) FROM T1

2、SPACE函数
语法：SPACE(SIZE)
SPACE函数返回一个包含SIZE个空格的字符串。

eg:
SELECT SPACE(10) FROM T1

截断字符串
1、SUBSTR函数
语法：SUBSTR(ARG1,POS,<LENGTH>)
SUBSTR函数返回ARG1中POS位置开始的LENGTH个字符，如果没有指定LENGTH，则返回剩余的字符。

substr()是指从position开始算（包括position这个字符），截取的长度，也包括位置+长度所在的字符(前提是字符串的长度大于或等于两者之和)。

eg:
SELECT SUBSTR('CDNJFDJFJD',5,2) FROM T1

转换为字符串
1、char函数 转化成字符串
char(100)——把100转化为100字符串内容
char(current date,ISO)——转换成yyyy-mm-dd
char(current date,USA)——转换成mm/dd/yyyy
char(current date,EUR)——转换成dd.mm.yyyy

三、分组函数

row_number() over()函数

分组排序求和函数，有一篇专门的文档记录

四、类型转换函数

1、CAST表达式用来转换类型使用
SELECT CAST(CURRENT TIME AS CHAR(8) ) FROM SYSIBM.SYSDUMMY1  2、转换为字符串类型：
CHAR函数
语法：CHAR(ARG)
CHAR函数返回日期时间型、字符串、整数、十进制或双精度浮点数的字符串表示。 
SELECT CHAR(SALARY) FROM TEST  

3、CHR函数

语法：CHR(ARG)

CHR函数返回由参数指定的ASCII码的字符，参数可以是INTEGER或SMALLINT。
SELECT CHR(167) FROM TEST



 4、VARCHAR函数

语法：VARCHAR(ARG,LENGTH)
VARCHAR函数返回字符串、日期型、图形串的可变长度的字符串表示。SELECT VARCHAR(NAME,50) FROM TEST
--50为截取长度，如果name字符串的长度大于50，则返回“SQL0445W 值已被截断。SQLSTATE=01004”。



5、DIGITS函数

语法：DIGITS()

DIGITS函数返回SMALLINT、INTEGER、BIGIT或者DECIMAL参数的字符串值。
SELECT DIGITS(ID) FROM TEST  
6、转换为数字类型：

DOUBLE、FLOAT函数

语法:DOUBLE()、FLOAT()

DOUBLE、FLOAT函数如果参数是一个数字表达式，返回与其对应的浮点数，否则返回错误代码。SELECT DOUBLE('4569') FROM TEST



7、INT函数

语法:INT()

INT函数返回整型常量中的数字、字符串或者日期、时间的整数表示。SELECT INT('111') FROM TEST



8、BIGINT函数

语法:BIGINT()

BIGINT函数返回整型常量中的数字、字符串或者时间戳的64位长整数表示。SELECT BIGINT('111') FROM TEST



9、SMALLINT函数

语法:SMALLINT()

SMALLINT函数返回整型常量中的数字、字符串短整数表示。SELECT SMALLINT('111') FROM TEST



10、REAL函数

语法:REAL()

REAL函数返回一个数值的单精度浮点数表示。SELECT REAL(10) FROM TEST



11、DEC[IMAL]函数

语法:DEC[IMAL]()

DEC[IMAL]函数返回一个数值、DECIMAL的字符串、INTEGER的字符串、FLOAT-POINT的字符串、日期、时间或时间戳的DECIMAL数值。SELECT DEC(10) FROM TEST



12、HEX函数

语法:HEX()

HEX函数返回一个字符串的值的16进制表示。SELECT HEX(10) FROM TEST



13、FLOOR函数

语法:FLOOR()
FLOOR函数小于或等于参数的最大整数。
SELECT FLOOR(10.50) FROM TEST