DB2 V95常用函数详解（带实例）

DB2常用函数实例版

使用说明:函数定义形式 result : schema-name.funcation_name(para-list)
  其中参数列表形式,<>表示必选参数,[]表示可选参数,|表示任选其一
  下面的所有实例均在DB2 V95版本测试通过，如果是新版本会加注。

any-builtin-type:Any data type that is not a distinct type
structured-type:Any user-defined structured type defined to the database
character-type:Any of the character string types: CHAR, VARCHAR, LONG VARCHAR, CLOB.
datetime-type:Any of the datetime types: DATE, TIME, TIMESTAMP
graphic-type:Any of the double byte character string types: GRAPHIC, VARGRAPHIC, LONGVARGRAPHIC, DBCLOB.
string-type:Any type from character type, graphic-type or BLOB
numeric-type:Any of the numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE.

函数定义的Schema有sysfun和sysibm

1 字符串函数
ascii 返回字符串首字符的ASCII码值
  integer : sysfun.ascii(||)
  values sysfun.ascii('A'),sysfun.ascii('ABC'),sysfun.ascii('中国人'); 结果：65 65 206
hex 返回字符的十六进制值  
  varchar : hex()
  values hex('中'),hex('A'),x'41',x'E590B4'; 结果:E590B4 41 A 中  
chr 返回ASCII值对应的字符
  char : sysfun.chr()
  values sysfun.chr(89); 结果:Y
concat 字符串连接  
  string-type : sysibm.concat(,)
  values sysibm.concat('hello','world');结果:helloworld
lower,lcase 转小写，upper,ucase 转大写
  varchar(4000)|clob(1M) : sysfun.lcase(|)
  char|varchar : sysibm.lower(|)
  values sysibm.lower('Your Name'),sysibm.upper('Your Name'); 结果:your name YOUR NAME
  values sysibm.lcase('Your Name'),sysfun.ucase('Your Name'); 结果:your name YOUR NAME
ltrim 去左空格，rtrim 去右空格，trim 去左右空格
  char|varchar|graphic sysibm.ltrim(||)
  varchar(4000)|clob(1M) sysfun.ltrim(|)
  rtrim定义同ltrim，只不过trim函数定义不清楚
  values '['||sysibm.ltrim(' h w ')||']','['||sysfun.rtrim(' h w ')||']','['||trim(' h w ')||']'; 结果:[h w ] [ h w] [h w]
trim 去除表达式前后指定的字符，默认是空格
  character-type : sysibm.trim([B|BOTH|L|LEADING|T|TRAILING] FROM ) 将表达式前或后(默认是BOTH)指定的字符(strip-express)移除
  values trim('0' from '00012300') 结果：123
strip 去除表达式前后指定的字符，默认是空格
  character-type : sysibm.strip([,B|BOTH|L|LEADING|T|TRAILING,])
  values strip('**88000***',b,'*') 结果：88000
posstr 查找字符串位置(从1开始计数),没有找到返回0
  string-type : sysibm.posstr(,) 前者为source-string，后为search-string
  values sysibm.posstr('the one is not that one','one'); 结果:5
position 查找字符串位置，与posstr功能类似，由于考虑到不同国家的字符集，增强编码模式
  string-type : sysibm.position(,,CODEUNITS16|CODEUNITS32|OCTETS)
  values position('a','abca',OCTETS) 结果：1
locate 搜索子字符串出现的位置，找不到返回0
  integer : sysfun.locate(,[,integer])  其中varchar(4000)还可以换成clob(1M)或blob(1M)
  values sysfun.locate('y','yesterday'),sysfun.locate('y','yesterday',2); 结果: 1  9  
repeat 重复字符串
  varchar(4000)|clob(1M)|blob(1M) : sysfun.repeat(||,)
  values sysfun.repeat('ab',5);  结果:ababababab
space 产生空格
  varchar(4000) : sysfun.space()
  values '['||sysfun.space(5)||']' ; 结果:[     ]
replace 替换字符串
  varchar(4000)|clob(1M)|blob(1M) : sysfun.replace(,,) 在exp1中查找所有exp2使用exp3来替换
  values sysfun.replace('abac','a','d'); 结果:dbdc      
left 返回左边字符串 right 返回右边字符串
  varchar(4000)|clob(1M)|blob(1M) : sysfun.left(|,) right函数同样定义
  values left('adf.adf',5),right('adf.adf',5) ; 结果:adf.a  f.adf
  values right(digits(3),5),left(rtrim(char(3))||'00000',5);  结果: 00003  30000  这也是左补零右补零的方法
substr 截取字符中
  string-type : sysibm.substr(,[,integer:length]) 要求start从1~string的长度，如果指定length的话，要求必须能取到length位字符，不然会报错。
  values sysibm.substr('abcdasd',1,2),sysibm.substr('abcdasd',3); 结果: ab  cdasd
substring 字符串截取函数，与substr用法类似，不同的是substr是按字节截取，substring可以指定string unit
  string-type : sysibm.substring(,[,],CODEUNITS16|CODEUNITS32|OCTETS)  其中CODEUNITS16按16-bit UTF-16的code unit，CODEUNITS32按32-bit UTF-32 code units，OCTETS则按字节
  values substring('中国人是我',1,4,CODEUNITS16),substring('中国人是我',1,6,OCTETS) 结果：'中国人是' '中国'
octet_length 返回字符串的字节数
  integer : sysibm.octet_length()
  values octet_length('abc中'); 结果：6
length 返回字符串的长度(字节)
  integer : sysibm.length(,[CODEUNITS16|CODEUNITS32|OCTETS])
character_length 功能同length函数，使用时必须输入字符码
  values length('abc中'),character_length('abc中',CODEUNITS32); 结果：6 4

2 数学函数
digits 返回固定长度(不够前导补零)仅数字的字符串，不包括符号和点，结果长度small integer5位，large integer10位，big integer19位，decimal类型p精度位
  string : sysibm.digits()
  values digits(smallint(2.3)),digits(2.3),digits(bigint(2.3)),digits(dec(2.3,8,2)); 结果是：'00002','23','0000000000000000002','00000230'      
abs、absval 取绝对值
  values abs(-23); 结果：23  
ceiling、floor 向上取整和向下取整
  values ceiling(13.56),floor(13.56); 结果：14 13
mod 取模，取余数
  values mod(10,3); 结果：1
rand 返回随机数
  values rand;
round 国舍五入
  values round(23.853,0),round(23.853,1),decimal(round(23.853,1),3,1) ,round(23.853,-1); 结果：24.000 23.900 23.9 20.000
sign 如果参数大于0返回1，如果参数小于0返回-1，如果参数为0返回0
power 返回参数1的参数2次幂
sqrt 返回该参数的平方根
degrees 求角度
radians 将度转换为弧度


3 日期函数
0)了解一下unix timestamp与DB2中的timestamp区别
Unix时间戳(Unix Timestamp):从格人威治时间1970年01月01日00时00分00秒(1970年1月1日 00:00:00 GMT)到现在的总秒数
即从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数，不考虑闰秒。一个小时为3600秒，一个基准日(纪元日,epoch day)为86400秒。以32位整型进行保存，可能会在2038年1月19日产生一些问题。

GMT:Greenwich Mean Time 格人威治时间
UTC:Universal Time Coordinated UTC时间，现在已经渐渐变成GMT的同义词

转换当前时间为GMT时间：current timestamp - current timezone

数据库中的timestamp与unix timestamp是不同的，即数据库中的timestamp并不是时间戳，它是一个日期时间类型记录的是日期和时间信息。
1)DB2中关于日期与时间的表示有timestamp日期和时间(其中时间部分精确到ff6,即1000000ms)，date日期，time时间(精确到秒)
oracle中的timestamp类型精确到ff9毫秒
2)寄存器中的日期与时间
current timestamp 当前时间戳
current date 当前日期
current time 当前时间
3)日期时间对象直接相减，返回timestamp duration(数据类型为dec(20,6)，即yyyymmddhhmiss.ff6)、date duration(数据类型为dec(8,0)，即yyyymmdd)和time duration(数据类型为dec(6,0)，即hhmiss)
timestamp-timestamp 返回两个时间戳对象相差的年月日时分秒毫秒
date-date 返回两个日期相差年月日
time-time 返回两个时间相差的时分秒
values date('2011-02-13')-date('2010-01-12'); 返回1 01 01，也就是1年1个月1日
values date('2998-02-13')-date('1220-08-26'); 返回1777 05 18，也就是1777年5个月18日
4)DB2中日期与时间对象能直接与带单位的数值(忽略小数部分)相加减
年years、月months、日days、小时hours、分钟minutes、秒seconds、毫秒microseconds
values current time + 3 hours - 30 minutes + 58 seconds ;  结果：02:20:30
values current date + 2 years - 2 months + 20 days;  结果：2013-12-03
values current timestamp + 2 years - 2 months + 20 days + 3 hours - 30 minutes + 58 seconds; 结果：2013-12-04 02:20:30
5)日期时间字符串转日期时间对象
timestamp、date、time、year、month、day、hour、minute、second、microsecond
这些函数的参数为timestmap、date、time、timestamp duration、date duration、time duration以及有效的时间日期字符串，
结果依次返回参数的timestamp、日期、时间、年、月、日、小时、分钟、秒和毫秒部分
SELECT timestamp('2012-11-23 18:35:30.580000') col1,
       date('2012-11-23') col2,
       time('18:35:30') col3,
       current timestamp col17,
       current date col18,
       current time col19,
       year(current timestamp) col4,
       year(current date) col5,
       month(current timestamp) col6,
       month(current date) col7,
       day(current timestamp) col8,
       day(current date) col9,
       hour(current timestamp) col10,
       hour(current time) col11,
       minute(current timestamp) col12,
       minute(current time) col13,
       second(current timestamp) col14,
       second(current time) col15,
       microsecond(current timestamp) col16
  FROM sysibm.sysdummy1;
6)其它日期时间函数
days 返回自0001年1月1日以来的天数，可以用来计算两个日期相隔的天数
  bigint : sysibm.days(timestamp|date|date-string)
  values days(current date)-days(date('2012-11-02')); 结果：21
timestamp_format 根据格式化字符串和日期字符串返回timestamp
  timestamp : sysibm.timestamp_format(,) ，其中exp1表示日期字符串，exp2表示格式化字符串
  其中支持的日期时间格式化字符串有：yyyy 4位年、mm 2位月 、dd 2位月、hh12 12小时制小时、hh24 24小时制小时、mi 2位分钟、ss 2位秒、ff 1~6位毫秒
  格式化分隔符有dash (-)、period (.)、slash (/)、comma (,)、apostrophe (')、semi-colon (;)、colon (:)、blank ( )
  SELECT timestamp_format('2012/11/23 235935.540540','yyyy/mm/dd hh24miss.ff6') col1,
       timestamp_format('20121123','yyyymmdd') col2,
       timestamp_format('23,59','hh24,mi') col3
  FROM sysibm.sysdummy1;
to_timestamp和to_date 字符串转timestamp和date类型，是timestamp_format的同义词，用法类似  
timestampdiff 计算返回表达式中指定部分的值
  integer:sysfun.timestampdiff(,) 参数exp1是指定值1(毫秒)|2(秒)|4(分钟)|8(小时)|16(天)|32(周)|64(月)|128(季)|256(年)，参数exp2表示timestamp duration的char(22)字符串
  values timestampdiff(16,char(current timestamp - (current timestamp - 23 days)));
  注意：1、此函数在计算时按1月=30天计算，因此计算天不准确，建议使用days()计算相差的天数，例如下面计算2011-03-13与2010-01-26相差天数，结果为413和411，明显示411是正确的，而413是将2010年2月的28天当30天多算了2天
          values timestampdiff(16,char(timestamp('2011-03-13 03:16:34.562003')-timestamp('2010-01-26 00:00:00')));
          values days(timestamp('2011-03-13 03:16:34.562003')) - days(timestamp('2010-01-26 00:00:00'));
        2、因为返回值为integer类型，所以函数在计算时有精度误差，它是只舍不入的，例如下面结果4，实际上应该是4分钟59秒
          values timestampdiff(4,char(current timestamp - (current timestamp - 5 minutes + 1 seconds)));
quarter 返回日期所在季度，返回1~4
  values quarter(current date) ;         
midnight_seconds 返回自午夜过去的秒数，即从00:00:00到指定时间间隔秒数
  SELECT current timestamp col1,
       midnight_seconds(current timestamp) col2,
       midnight_seconds(current time) col3
  FROM sysibm.sysdummy1;
monthname 返回月分名称
  values monthname(current date);         
dayofyear 返回日期在当年中的第几天，返回1~366
dayofweek 返回日期在周中的位置
  integer : sysfun.dayofweek(timestamp|date|date-string) 返回1~7，分别表示星期天、星期一...星期六
  values dayofweek(current date);
dayname 返回日期是星期几  
  string : sysfun.dayname(timestamp|date|date-string) 返回本地星期的表示，如星期一
week 返回日期是当年的第几周，返回1~54
last_day 返回日期当月的最后一天日期，在v9.7中使用s


4 类型转换函数
在DB2中内置数据类型都有自己相应的数据类型转换函数，如smallint、integer、bigint、decimal、real、double、float、char、varchar、clob、graphic、blob等
因为其使用简单，不作详述

decimal <--> char
  假设字段定义为decimal(20,5)，现在存储8.9，直接转结果是字符串为'000000000000008.90000 '，注意后面有一个空格
    values char(cast(8.9 as decimal(20,5)));
  最佳写法：values strip(trim(char(cast(8.9 as decimal(20,5)))),both,'0'); 结果是字符串'8.9'
  字符串'8.9'转成decimal(10,5)写法  values decimal('8.94',10,2);或者values cast('8.934' as decimal(10,5));
cast 数据类型转换
  data-type : cast( as )
  values cast('123' as integer);
decimal或者dec
  decimal : sysibm.dec([,integer[,integer]]) 返回数值的小数显示形式
    参数说明：numeric-type 数值类型,integer1 精度(precision),integer2 该度(scale)，就是显示的小数位数
    如果precision设置过小，会报SQLSTATE=22003错误，数值转换时越界，即要求整数部分位数 < precision - scale，
    例如decimal(223.345,6,2)中6定义一共6位数字，2定义两位小数，如果需要转换的字符串非小数部分位数超6-2就会报错 。
  decimal : sysibm.dec([,integer[,integer[,varchar]]]) 将字符串转成小数形式的数值，需要指定decimal-character（最后一个参数)
  values sysibm.dec(233.2323,4,1);      结果:233.2
  values sysibm.dec(233,4,1);           结果:233.00
  values sysibm.dec('23.2323',5,1,'.'); 结果:23.20
  values sysibm.decimal(223.345,6,2);   结果:223.34
char 转字符串
  char : sysibm.char([,varchar]) 返回数值的字符串格式，如果指定varchar字符串的话，那么decimal中的点按指定字符串显示
  char : sysibm.char([,integer])  返回字符串，如果指定integer的话就按长度返回
  char : char([,keywords])  返回日期的字符串，按keywords格式化
    DB2日期的缺省格式由数据库的地区代码决定，该代码在数据库创建的时候被指定。可以在创建数据库时使用 territory=USA 来定义地区代码，可选值有:
      DEF 使用与地区代码相匹配的日期和时间格式
      EUR 使用欧洲日期和时间的 IBM 标准格式，格式:dd.mm.yyyy
      ISO 使用国际标准组织（ISO）制订的日期和时间格式，格式:yyyy-mm-dd
      JIS 使用日本工业标准的日期和时间格式,格式为:yyyy-mm-dd
      LOC 使用与数据库地区代码相匹配的本地日期和时间格式，本地日期格式，在char(date-type,keywords)中使用LOCATE
      USA 使用美国日期和时间的 IBM 标准格式，格式:mm/dd/yyyy
  values sysibm.char(223.345,',') ;     结果:223,345
  values sysibm.char('223.345',4) ;     结果:223.
  values char(current date,ISO);        结果:2012-05-09
  values sysibm.char(current date);     结果:2012-05-09
  values sysibm.char(current timestamp);结果:2012-05-09-23.22.03.906000
  values sysibm.char(34);               结果:34
to_char 将timestamp类型参数转成指定格式字符串
  定义: varchar : sysibm.to_char(,)
  实例: values sysibm.to_char(current timestamp,'yyyy-mm-dd hh:mi:ss'); 结果:2012-05-10 01:24:43


5 其它函数
coalesce 取第一个非空值，可以用来将NULL转成指定值
  any-type : sysibm.coalesce(par1,par2,...) 要求参数类型一致，可以接受任何build-in data type
  values coalesce(cast(null as integer),2,3);
value 取第一个非空值，用法类似于coalesce  
nullif 参数相等时返回NULL，否则返回第一个参数
  any-type : sysibm.nullif(,)
      nullif(e1,e2) 相当于 case when e1=e2 then null else e1 end
  values sysibm.nullif('a','a'),sysibm.nullif(cast(null as char(1)),'a');
数值截断函数 trunc或truncate
  decimal-type : sysfun.trunc(,)
       其中exp2为正数时截断小数位(保留小数位)，为负数时向左截断整数位(变0)
  values sysfun.truncate(345.2344,2),truncate(345.2344,56),truncate(345.2344,0),truncate(345.2344,-1); 结果:345.23 345.2344 345 340
translate 字符串转换
  varchar : sysibm.translate(,,[,varchar:pad-string])
      按自左到右顺序，按位找from-string中的字符在source-string出现时使用同位置(与from-string中字符相同位置)的字符替换，
      如果在from-string中的位置在to-string中没有，则使用pad-string字符串(默认是空格)替换
  values sysibm.translate('Hello World','db2','World','%'); 结果:He%%b db2%%
        解释:from-string为World，to-string为db2，替换字符串为%，就是在Hello World中替换W-->d,o-->b,r-->2,l-->%,d-->%
        vlaues '['||sysibm.translate('Hello World','db2','World')||'] ; 结果:[He  b db2  ]
listagg 聚集函数
DB2 v9.7.4 中新增的函数，用于拼
SELECT id,LISTAGG(name,',') WITHIN GROUP(ORDER BY name) names
  FROM (VALUES(1,'张三'),(1,'李明'),(1,'杨青'),(2,'柯言'),(2,'何枫')) AS t(id,name)
GROUP BY id;
结果：
id name
-- ---------------
1 张三,李明,杨青
2 何枫,柯言

SELECT stno,LISTAGG(subname||' '||score,',') WITHIN GROUP(ORDER BY 1) info
  FROM (VALUES ('NO01','语文',90) , ('NO01','数学',99) , ('NO01','英语',98) ,                                                         
               ('NO02','语文',96),('NO02','数学',95)) AS t(stno,subname,score)
GROUP BY stno;
结果：
STNO       INFO
-------    -----------
NO01       语文 90,数学 99,英语 98
NO02       语文 96,数学 95


xmlagg 函数
SELECT stno,REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME stno,subname||' '||score||' , '))),''),'') info
  FROM (VALUES ('NO01','语文',90) , ('NO01','数学',99) , ('NO01','英语',98) ,                                                         
               ('NO02','语文',96),('NO02','数学',95)) AS t(stno,subname,score)
GROUP BY stno;

结果：
STNO       INFO
-------    ----------------------------------
NO01       语文 90 , 数学 99 , 英语 98 ,
NO02       语文 96 , 数学 95 ,

type_id、type_name和type_schema 返回数据类型的id、name和schema，要求参数是structured data type
strip 功能增加的trim标量函数，它可以用来去除表达式的前缀和后缀




特殊应用：
--获取当前月份
values month(current timestamp) ;
--获取当前日期是当前月的第几天
values day(current timestamp) ;
--获取当前月表天数
--获取上月最后一天
values current timestamp - day(current timestamp) days ;
--获取上月第一天
values current timestamp -1 months - day(current timestamp) days + 1 days ;
--获取本月第一天
values current timestamp - day(current timestamp) days + 1 days ;
values current timestamp - day(current timestamp) days - hour(current timestamp) hours - minute(current timestamp) minutes - second(current timestamp) seconds - microsecond(current timestamp) microsecond + 1 days ;
--获取本月最后一天
values current timestamp + 1 months - day(current timestamp) days ;
values current timestamp - day(current timestamp) days + 1 days + 1 months - 1 days ;
values current timestamp + 1 months - day(current timestamp) days - hour(current timestamp) hours - minute(current timestamp) minutes - second(current timestamp) seconds - microsecond(current timestamp) microsecond ;
values current date + 1 months - day(current date + 1 months) days ;
--获取下月第一天
values current timestamp + 1 months - day(current timestamp) days - hour(current timestamp) hours - minute(current timestamp) minutes - second(current timestamp) seconds - microsecond(current timestamp) microsecond + 1 days;


计算两个时间之间的差(秒)
CREATE FUNCTION second_diff(t1 timestamp, t2 timestamp)
RETURNS int
RETURN ((days(t1) - days(t2)) * 86400 +  (midnight_seconds(t1) - midnight_seconds(t2)));

调用: VALUES second_diff(timestamp('2012-05-10 12:50:23'),timestamp('2012-05-10 12:40:28'));

判断是否润年
CREATE FUNCTION daysinyear(yr INT)
RETURNS INT
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE
        CASE (mod(yr, 4))   WHEN 0 THEN
        CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END
        ELSE 365 END
   END);

调用: VALUES daysinyear(1998) ;

定制日期格式
DROP FUNCTION ts_fmt;
CREATE FUNCTION ts_fmt(ts timestamp,fmt varchar(20))
RETURNS varchar(50)
RETURN
  WITH tmp(dd,mm,yyyy,hh,mi,ss,nnnnnn) AS (
    SELECT substr(digits(day(ts)),9),
          substr(digits(month(ts)),9),
          rtrim(char(year(ts))),
          substr(digits(hour(ts)),9),
          substr(digits(minute(ts)),9),
          substr(digits(second(ts)),9),
          rtrim(char(microsecond(ts)))
      FROM sysibm.sysdummy1         
  )
  SELECT CASE fmt WHEN 'yyyymmdd' THEN yyyy||mm||dd
                  WHEN 'mm/dd/yyyy' THEN mm||'/'||dd||'/'||yyyy
                  WHEN 'yyyy/dd/mm hh:mi:ss' THEN yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'|| ss
                  WHEN 'yyyy-dd-mm hh:mi:ss' THEN yyyy||'-'||mm||'-'||dd||' '||hh||':'||mi||':'|| ss
                  ELSE 'date format ' || coalesce(fmt,' ') || ' not recognized.'
        END
    FROM tmp ;
调用: VALUES ts_fmt(current timestamp,'yyyy-dd-mm hh:mi:ss');