DB2常用函数

DB2常用函数

一、寄存器使用

使用方式：SYSIBM.SYSDUMMY1、SYSIBM.DUAL、values

使用示例：

SELECT 'hello db2' FROM SYSIBM.SYSDUMMY1; 
SELECT 'hello db2' FROM SYSIBM.DUAL; 
VALUES 'hello db2'; 

二、时间处理相关

1、日期与时间

SELECT CURRENT DATE FROM SYSIBM.DUAL; --使用寄存器方式
VALUES CURRENT DATE ;  --查日期
VALUES CURRENT TIME ;  --查时间
VALUES CURRENT TIMESTAMP; --查时间戳

2、年月日时分秒

年月日时分秒相关函数

YEAR()    --获取年；
MONTH()    --获取月；
DAY()    --获取日；
HOUR()   --获取小时；
MINUTE()    --获取分钟；
SECOND()   --获取秒；
DATE()   --获取日期；
TIME()    --获取时间；
TIMESTAMP()   --获取时间戳。

示例：

 --使用寄存器方式获取当前年份
SELECT YEAR(CURRENT DATE) FROM SYSIBM.DUAL;
--values方式获取当前月份
VALUES MONTH（CURRENT DATE) ; 

--表示查当前月份的日志记录
select * from DBCM.LOGINFO L WHERE MONTH(L.OP_TIME)= MONTH(CURRENT DATE) and MONTH(L.OP_TIME) = MONTH(CURRENT DATE)

参数也可以是指定字符串

SELECT YEAR('2020-06-01') FROM SYSIBM.DUAL;
VALUES HOUR（'2020-06-01 20:20:06') ; 

3、年月日时分秒加减运行

还可以直接运行操作：要求加减带单位的时间长度。

VALUES TIMESTAMP('2020-06-01 22:18:12')+1 YEAR +1 MONTH +1 DAY +1 HOUR +1 MINUTE +1 SECOND ;
--结果：2021-07-02 23:19:13

VALUES TIMESTAMP('2020-05-30 21:18:12')-1 YEAR -1 MONTH -1 DAY -1 HOUR -1 MINUTE -1 SECOND ;
--结果：2019-04-29 20:17:11

--表示查询当前月份的英文名称
SELECT MONTHNAME(CURRENT date)  FROM SYSIBM.DUAL;

以LOGINFO表为例：

--查询今年上个月的日志
select * from DBCM.LOGINFO L WHERE YEAR(L.OP_TIME)= YEAR(CURRENT DATE) and MONTH(L.OP_TIME) =(MONTH(CURRENT DATE)-1 )

4、日期相关相关函数

WEEK()  --判断时间年份及当前该年第几周
MONTHNAME()  --获取月份英文名称
DAY()       --判断时间是该月的第几天
DAYNAME()   --判断传入时间是周几的英文名称
DAYOFYEAR() --判断传入时间是该年的第几天
DAYOFMONTH()  --判断传入时间是该月的第几天
DAYOFWEEK()    --判断传入时间是该周的第几天
days()      --可以获取两个时间相差天数

使用示例：

WEEK():

VALUES week('2020-06-10') ; --结果：24,表示传入时间是2020年第24周

MONTHNAME()：

VALUES MONTHNAME('2020-06-10') ; --结果：June

DAY():

VALUES day('2020-06-10') ; --结果：10，6月的第10天

DAYNAME():

VALUES DAYNAME('2020-06-10') ; --结果：Wednesday

DAYOFYEAR():

VALUES DAYOFYEAR('2020-06-10') ;--结果：162

DAYOFMONTH():

VALUES DAYOFMONTH('2020-06-10') ;--结果：10，6月的第10天

DAYOFWEEK():

VALUES DAYOFWEEK('2020-06-10') ;--结果：4, 默认一周日为第一天，周三是第4天
VALUES DAYOFWEEK('2020-06-14') ;--结果：1

DAYOFWEEK_ISO():可以和DAYOFWEEK()比较一下：

VALUES DAYOFWEEK_ISO('2020-06-10') ;--结果：3
VALUES DAYOFWEEK_ISO('2020-06-14') ;--结果：7

DAYS():

VALUES DAYS(CURRENT DATE)-DAYS(CURRENT DATE-10 DAY);
--结果：10 ，可以计算日期差多少天

values DAYNAME(current timestamp)

TIMESTAMPDIFF:

TIMESTAMPDIFF(n,CHAR(TIMESTAMP('2012-05-25 10:23:24')-TIMESTAMP('2012-05-25 10:20:24')))

根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。（整除部分，不会四舍五入）其中n的含义如下：

n=2：秒 ； n=4 ：分； n=8：时；

n=16 ：天； n=32 ：周；

n=64：月； n=128 ：季度； n=256:年；

使用示例：

VALUES TIMESTAMPDIFF(16, char(TIMESTAMP('2020-06-14 12:12:12')-TIMESTAMP('2020-06-10 12:12:12')));
--结果：4 ，表示相差4天

4、时间的格式

ISO，使用国际标准组织（ISO）制订的日期和时间格式；USA，使用美国日期和时间的 IBM 标准格式。

values char(current date,iso); --2020-06-10
values char(current date,usa); --06/10/2020
values char(current time,iso); --18.08.53

三、字符串函数

1、日期时间转换

（1）TO_CHAR()把时间转换成指定格式的字符串类型。低版本DB2不支持。

“YYYY-MM-DD HH:MI:SS” 十二小时制；

“YYYY-MM-DD HH24:MI:SS”二十四小时制。

SELECT TO_CHAR(TIMESTAMP('2020-06-10 12:18:12'),'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.DUAL;
--结果：2020-06-10 12:18:12

（2）char(arg)把参数转化成字符串

values char(100);  --把100转化为100字符串内容

2、LENGTH函数

语法

LENGTH(ARG)

LENGTH函数返回参数ARG的长度。

SELECT LENGTH(LOG_MSG) FROM DBCM.LOGINFO

3、CONCAT函数

字符串连接函数。

SELECT CONCAT(CONCAT('hello',' world'),' !')  as test
FROM SYSIBM.DUAL;
--结果：hello world !

4、去空格函数

LTRIM、RTRIM函数去空格

DB2中无trim()函数，所以要去空格要执行左右去空格。

LTRIM()：LTRIM函数从CHAR、VARCHAR、GRAPHIC或者VARGRAPHIC中去掉左侧空格。
RTRIM()：RTRIM函数则去去掉右侧的空格。

SELECT LTRIM('   hello 1'),RTRIM(' 2world !  ') FROM SYSIBM.DUAL;

5、截取/截断函数

（1）SUBSTR函数

语法：

SUBSTR(ARG1,POS,<LENGTH>)

SUBSTR函数返回ARG1中POS位置开始的LENGTH个字符，如果没有指定LENGTH，则返回剩余的字符。

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

SELECT SUBSTR('helloworld',5,2) FROM SYSIBM.DUAL; --结果：ow

（2）LEFT、RIGHT函数

LEFT(ARG,LENGTH)：LEFT函数返回ARG最左边LENGTH个字符串，ARG可以是CHAR或BINARY STRING。

RIGHT(ARG,LENGTH)：RIGHT函数返回ARG最右边的LENGTH个字符串

SELECT LEFT('hello',2),RIGHT('world',2) FROM SYSIBM.DUAL;
--结果： he  ld

6、大小写转换

LCASE、LOWER、UCASE、UPPER函数

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

SELECT LCASE('heLLo'),LOWER('WORLD') FROM SYSIBM.DUAL; --结果：hello  world

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

SELECT UCASE('heLLo'),UPPER('world') FROM SYSIBM.DUAL; --结果：HELLO  WORLD

7、替换函数

（a）INSERT函数

语法：

INSERT(ARG1,POS,SIZE,ARG2)   --找到位置替换

INSERT函数返回一个字符串，将ARG1从POS处删除SIZE个字符，将ARG2插入该位置。

（b）REPLACE函数

语法：

REPLACE(EXP1,EXP2,EXP3)

REPLACE函数用EXP3代替EXP1中所有的EXP2。

SELECT REPLACE('ROMANND'，'NND','CCB') FROM SYSIBM.DUAL;

8、条件查找字符串

根据条件查找字符串，返回字符串。

（a）REPEAT函数

语法：

REPEAT(ARG1,NUM_TIMES)

REPEAT函数返回ARG1被重复NUM_TIMES次的字符串。

SELECT REPEAT('hello'，2) FROM SYSIBM.DUAL;

（b）SPACE函数

语法：

SPACE(SIZE)

SPACE函数返回一个包含SIZE个空格的字符串。

SELECT SPACE(10) FROM SYSIBM.DUAL; --结果没发打，就是10个空格字符串

四、判断函数

1、VALUE函数

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

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

2、COALESCE函数

处理null的函数。

COALESCE(ARG1,ARG2)
--COALESCE返回参数集中第一个非null参数。
--用法类似于VALUE函数。当其第一个参数非空，直接返回该参数的值，如果第一个参数为空，则返回第二个参数的值

select COALESCE(COUNT_AMT, '0.00') from LOGINFO ;
--金额为null的时候，显示0.00

3、NVL函数

处理null的函数。

NVL(exp1, exp2)
--如果exp1为NULL，则函数返回exp2，否则返回exp1本身。但此函数有一定局限，所以就有了NVL2函数。

select nvl(COUNT_AMT, '0.00') from LOGINFO ;

NVL2函数：Oracle/PLsql中的一个函数,Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(exp1, exp2, exp3)的功能为：如果exp1为NULL，则函数返回exp3，若exp1不为null，则返回exp2。

4、CAST函数

与char有类似功能。可以用来转换类型。

SELECT CAST(CURRENT TIME AS CHAR(8) ) FROM SYSIBM.SYSDUMMY1

5、VARCHAR函数

语法：

VARCHAR(ARG,LENGTH)

VARCHAR函数返回ARG字符串、日期型、图形串的可变长度LENGTH的字符串表示。

SELECT VARCHAR('helloworld',5) FROM SYSIBM.DUAL;

6、CASE-WHEN函数

SELECT  id,USER_ID, 
(case 
when(COUNT_AMT<=10000)then '一般'
when(COUNT_AMT>10000  and  COUNT_AMT<=50000 )   then '普通'   
when(COUNT_AMT>50000  and COUNT_AMT<=100000 )   then '富裕' 
else   '土豪'  end ) AS status
FROM  DBCM.LOGINFO 

7、exists(A)函数

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

在sql中再具体使用示例说明。

8、判断字符位置

（a）instr函数

语法：

instr(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)

数据库中字符串索引都是从1开始，从0开始的话是搜索不到的。

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

values instr('hello world','o',0,1);    --DB2执行报错
values instr('hello world','o',1,1);    --结果：5
values instr('hello world','o',1,2);    --结果：8

select instr('hello world','o',-1,1) from SYSIBM.DUAL;  --结果：8

（b）LOCATE函数
语法：

LOCATE(ARG1,ARG2,<POS>)  --只有起始位置

LOCATE函数在ARG2中查找ARG1第一次出现的位置，如果指定POS，则从ARG2的POS处开始查找ARG1第一次出现的位置。

SELECT  LOCATE('l', 'hello world') from SYSIBM.DUAL; --结果：3

（c）POSSTR函数

语法：

POSSTR(EXP1,EXP2)

POSSTR函数返回EXP2在EXP1中的位置。 –没有起始位置和结束位置 SELECT LOCATE(NAME，’a’) FROM T1

SELECT  POSSTR('hello world','wo') FROM SYSIBM.DUAL; --结果：7

五、其他

（1）row_number() over()函数

排序、分页常用。

排序：

select *,rownumber() over() as row_id from  LOGINFO  

分页：

select * from (
select *,rownumber() over() as row_id from  LOGINFO  
) where row_id between 1 and 10

（2）DIGITS函数：

DIGITS函数返回SMALLINT、INTEGER、BIGIT或者DECIMAL参数的字符串值。

SELECT DIGITS('123') FROM SYSIBM.DUAL;

（3）DOUBLE、FLOAT函数:

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

SELECT DOUBLE('4569')  FROM SYSIBM.DUAL;

（4）INT函数

INT函数返回整型常量中的数字、字符串或者日期、时间的整数表示。

SELECT INT('123') FROM SYSIBM.DUAL;

（5）BIGINT函数

BIGINT函数返回整型常量中的数字、字符串或者时间戳的64位长整数表示。

SELECT BIGINT('789987') FROM SYSIBM.DUAL;

（6）SMALLINT函数

SMALLINT函数返回整型常量中的数字、字符串短整数表示。

SELECT SMALLINT('124') FROM SYSIBM.DUAL;

（7）REAL函数

REAL函数返回一个数值的单精度浮点数表示。

SELECT REAL(10) FROM SYSIBM.DUAL; --结果：10.0

（8）DEC[IMAL]函数

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

SELECT DEC(10.1314) FROM SYSIBM.DUAL; --结果：10

（9）HEX函数

HEX函数返回一个字符串的值的16进制表示。

SELECT HEX(10) FROM SYSIBM.DUAL; --结果：0A000000

（10）FLOOR函数

FLOOR函数取小于或等于参数的最大整数。

SELECT FLOOR(10.50) FROM SYSIBM.DUAL; --结果：10