DB2 V95常用函数详解(带实例)

1、DB2常用函数实例版使用说明:函数定义形式 result : schema-name.funcation_name(para-list)  其中参数列表形式,<>表示必选参数,表示可选参数,|表示任选其一  下面的所有实例均在DB2 V95版本测试通过，如果是新版本会加注。any-builtin-type:Any data type that is not a distinct typestructured-type:Any user-defined structured type defined to the databasecharacter-type:An

2、y of the character string types: CHAR, VARCHAR, LONG VARCHAR, CLOB.datetime-type:Any of the datetime types: DATE, TIME, TIMESTAMPgraphic-type:Any of the double byte character string types: GRAPHIC, VARGRAPHIC, LONGVARGRAPHIC, DBCLOB.string-type:Any type from character type, graphic-type or BLOBnumer

3、ic-type:Any of the numeric types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE.函数定义的Schema有sysfun和sysibm 1 字符串函数ascii 返回字符串首字符的ASCII码值  integer : sysfun.ascii(<char>|<varchar(4000)>|<clob(1M)>)  values sysfun.ascii('A'),sysfun.ascii('ABC'),sysfun.

4、ascii('中国人'); 结果：65 65 206hex 返回字符的十六进制值   varchar : hex(<any-type>)  values hex('中'),hex('A'),x'41',x'E590B4' 结果:E590B4 41 A 中 chr 返回ASCII值对应的字符  char : sysfun.chr(<integer>)  values sysfun.chr(89); 结果:Yconcat 字符串连接&#

5、160;  string-type : sysibm.concat(<string-type>,<string-type>)  values sysibm.concat('hello','world');结果:helloworldlower,lcase 转小写，upper,ucase 转大写  varchar(4000)|clob(1M) : sysfun.lcase(<varchar(4000>|<clob(1M)>)  char|varchar : sysibm.lowe

6、r(<char>|<varchar>)  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 NAMEltrim 去左空格，rtrim 去右空格，trim 去左右空格  char|varchar|graphic

7、 sysibm.ltrim(<char>|<varchar>|<graphic>)  varchar(4000)|clob(1M) sysfun.ltrim(<varchar(4000)>|<clob(1M)>)  rtrim定义同ltrim，只不过trim函数定义不清楚  values ''|sysibm.ltrim(' h w ')|'',''|sysfun.rtrim(' h w ')|'','&

8、#39;|trim(' h w ')|'' 结果:h w h w h wtrim 去除表达式前后指定的字符，默认是空格  character-type : sysibm.trim(B|BOTH|L|LEADING|T|TRAILING <strip-express> FROM <string-express>) 将表达式前或后(默认是BOTH)指定的字符(strip-express)移除  values trim('0' from '00012300') 结果：123strip 去除表达

9、式前后指定的字符，默认是空格  character-type : sysibm.strip(<string-express>,B|BOTH|L|LEADING|T|TRAILING,<strip-express>)  values strip('*88000*',b,'*') 结果：88000posstr 查找字符串位置(从1开始计数),没有找到返回0  string-type : sysibm.posstr(<string-type:source-string>,<string-type:

10、search-string>) 前者为source-string，后为search-string  values sysibm.posstr('the one is not that one','one'); 结果:5position 查找字符串位置，与posstr功能类似，由于考虑到不同国家的字符集，增强编码模式  string-type : sysibm.position(<string-type:source-string>,<string-type:search-string>,CODEUNITS16|C

11、ODEUNITS32|OCTETS)  values position('a','abca',OCTETS) 结果：1locate 搜索子字符串出现的位置，找不到返回0  integer : sysfun.locate(<varchar(4000):exp1>,<varchar(4000):exp2>,integer)  其中varchar(4000)还可以换成clob(1M)或blob(1M)  values sysfun.locate('y','yesterday'

12、;),sysfun.locate('y','yesterday',2); 结果: 1  9 repeat 重复字符串  varchar(4000)|clob(1M)|blob(1M) : sysfun.repeat(<varchar(4000)>|<clob(1M)>|<blob(1M)>,<integer>)  values sysfun.repeat('ab',5);  结果:abababababspace 产生空格  varchar(

13、4000) : sysfun.space(<integer>)  values ''|sysfun.space(5)|'' ; 结果:     replace 替换字符串  varchar(4000)|clob(1M)|blob(1M) : sysfun.replace(<varchar(4000):exp1>,<varchar(4000):exp2>,<varchar(4000):exp3>) 在exp1中查找所有exp2使用exp3来替换 

14、values sysfun.replace('abac','a','d'); 结果:dbdc     left 返回左边字符串 right 返回右边字符串  varchar(4000)|clob(1M)|blob(1M) : sysfun.left(<varchar(4000)>|<clob(1M)>,<integer>) right函数同样定义  values left('adf.adf',5),right('adf

15、.adf',5) ; 结果:adf.a  f.adf  values right(digits(3),5),left(rtrim(char(3)|'00000',5);  结果: 00003  30000  这也是左补零右补零的方法substr 截取字符中  string-type : sysibm.substr(<string-type:string>,<integer:start>,integer:length) 要求start从1string的长度，如果指定length的话，要求必

16、须能取到length位字符，不然会报错。  values sysibm.substr('abcdasd',1,2),sysibm.substr('abcdasd',3); 结果: ab  cdasdsubstring 字符串截取函数，与substr用法类似，不同的是substr是按字节截取，substring可以指定string unit  string-type : sysibm.substring(<string-type:string>,<integer:start>,<integer:lengt

17、h>,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.

18、octet_length(<any-builtin-type>)  values octet_length('abc中'); 结果：6length 返回字符串的长度(字节)  integer : sysibm.length(<any-builtin-type>,CODEUNITS16|CODEUNITS32|OCTETS)character_length 功能同length函数，使用时必须输入字符码  values length('abc中'),character_length('abc中',

19、CODEUNITS32); 结果：6 42 数学函数digits 返回固定长度(不够前导补零)仅数字的字符串，不包括符号和点，结果长度small integer5位，large integer10位，big integer19位，decimal类型p精度位  string : sysibm.digits(<numeric-type>)  values digits(smallint(2.3),digits(2.3),digits(bigint(2.3),digits(dec(2.3,8,2); 结果是：'00002','23',&

20、#39;0000000000000000002','00000230'      abs、absval 取绝对值  values abs(-23); 结果：23 ceiling、floor 向上取整和向下取整  values ceiling(13.56),floor(13.56); 结果：14 13mod 取模，取余数  values mod(10,3); 结果：1rand 返回随机数  values rand;round 国舍五入  values r

21、ound(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.000sign 如果参数大于0返回1，如果参数小于0返回-1，如果参数为0返回0power 返回参数1的参数2次幂sqrt 返回该参数的平方根degrees 求角度radians 将度转换为弧度3 日期函数0)了解一下unix timestamp与DB2中的timestamp区别Unix时间戳(Unix Timestamp):从格人威治时间1970年01月01日00时00分00秒(1970

22、年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是不同的，

23、即数据库中的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(

24、数据类型为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

25、，也就是1777年5个月18日4)DB2中日期与时间对象能直接与带单位的数值(忽略小数部分)相加减年years、月months、日days、小时hours、分钟minutes、秒seconds、毫秒microsecondsvalues current time + 3 hours - 30 minutes + 58 seconds ;  结果：02:20:30values current date + 2 years - 2 months + 20 days;  结果：2013-12-03values current timestamp + 2 years - 2 mont

26、hs + 20 days + 3 hours - 30 minutes + 58 seconds; 结果：2013-12-04 02:20:305)日期时间字符串转日期时间对象timestamp、date、time、year、month、day、hour、minute、second、microsecond这些函数的参数为timestmap、date、time、timestamp duration、date duration、time duration以及有效的时间日期字符串，结果依次返回参数的timestamp、日期、时间、年、月、日、小时、分钟、秒和毫秒部分SELECT timestamp(

27、'2012-11-23 18:35:30.580000') col1,       date('2012-11-23') col2,       time('18:35:30') col3,       current timestamp col17,       current date col18,

28、0;      current time col19,       year(current timestamp) col4,       year(current date) col5,       month(current timestamp) col6,       month(current dat

29、e) col7,       day(current timestamp) col8,       day(current date) col9,       hour(current timestamp) col10,       hour(current time) col11,       m

30、inute(current timestamp) col12,       minute(current time) col13,       second(current timestamp) col14,       second(current time) col15,       microsecond(current timestamp) col1

31、6  FROM sysibm.sysdummy1;6)其它日期时间函数days 返回自0001年1月1日以来的天数，可以用来计算两个日期相隔的天数  bigint : sysibm.days(timestamp|date|date-string)  values days(current date)-days(date('2012-11-02'); 结果：21timestamp_format 根据格式化字符串和日期字符串返回timestamp  timestamp : sysibm.timestamp_format(<exp1:st

32、ring>,<exp2:string>) ，其中exp1表示日期字符串，exp2表示格式化字符串  其中支持的日期时间格式化字符串有：yyyy 4位年、mm 2位月 、dd 2位月、hh12 12小时制小时、hh24 24小时制小时、mi 2位分钟、ss 2位秒、ff 16位毫秒  格式化分隔符有dash (-)、period (.)、slash (/)、comma (,)、apostrophe (')、semi-colon (;)、colon (:)、blank ( )  SELECT timestamp_format('201

33、2/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和

34、to_date 字符串转timestamp和date类型，是timestamp_format的同义词，用法类似 timestampdiff 计算返回表达式中指定部分的值  integer:sysfun.timestampdiff(<exp1:integer>,<exp2:char(22)>) 参数exp1是指定值1(毫秒)|2(秒)|4(分钟)|8(小时)|16(天)|32(周)|64(月)|128(季)|256(年)，参数exp2表示timestamp duration的char(22)字符串  values timestampdiff(

35、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

36、-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类型，所以函数在计算时有

37、精度误差，它是只舍不入的，例如下面结果4，实际上应该是4分钟59秒          values timestampdiff(4,char(current timestamp - (current timestamp - 5 minutes + 1 seconds);quarter 返回日期所在季度，返回14  values quarter(current date) ;         midnight

38、_seconds 返回自午夜过去的秒数，即从00:00:00到指定时间间隔秒数  SELECT current timestamp col1,       midnight_seconds(current timestamp) col2,       midnight_seconds(current time) col3  FROM sysibm.sysdummy1;monthname 返回月分名称  values monthname(curre

39、nt date);        dayofyear 返回日期在当年中的第几天，返回1366dayofweek 返回日期在周中的位置  integer : sysfun.dayofweek(timestamp|date|date-string) 返回17，分别表示星期天、星期一.星期六  values dayofweek(current date);dayname 返回日期是星期几   string : sysfun.dayname(timestamp|date|date-s

40、tring) 返回本地星期的表示，如星期一week 返回日期是当年的第几周，返回154last_day 返回日期当月的最后一天日期，在v9.7中使用s4 类型转换函数在DB2中内置数据类型都有自己相应的数据类型转换函数，如smallint、integer、bigint、decimal、real、double、float、char、varchar、clob、graphic、blob等因为其使用简单，不作详述decimal <-> char  假设字段定义为decimal(20,5)，现在存储8.9，直接转结果是字符串为'000000000000008.90000 &

41、#39;，注意后面有一个空格    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);

42、cast 数据类型转换  data-type : cast(<exp> as <data-type>)  values cast('123' as integer);decimal或者dec  decimal : sysibm.dec(<numeric-type>,integer,integer) 返回数值的小数显示形式    参数说明：numeric-type 数值类型,integer1 精度(precision),integer2 该度(scale)，就是显示的小数位数 

43、;   如果precision设置过小，会报SQLSTATE=22003错误，数值转换时越界，即要求整数部分位数 < precision - scale，    例如decimal(223.345,6,2)中6定义一共6位数字，2定义两位小数，如果需要转换的字符串非小数部分位数超6-2就会报错 。  decimal : sysibm.dec(<varchar>,integer,integer,varchar) 将字符串转成小数形式的数值，需要指定decimal-character（最后一个参数)  valu

44、es 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);   结果:22

45、3.34char 转字符串  char : sysibm.char(<decimal-type>,varchar) 返回数值的字符串格式，如果指定varchar字符串的话，那么decimal中的点按指定字符串显示  char : sysibm.char(<character-type>,integer)  返回字符串，如果指定integer的话就按长度返回  char : char(<date-type>,keywords)  返回日期的字符串，按keywords格式化    D

46、B2日期的缺省格式由数据库的地区代码决定，该代码在数据库创建的时候被指定。可以在创建数据库时使用 territory=USA 来定义地区代码，可选值有:      DEF 使用与地区代码相匹配的日期和时间格式      EUR 使用欧洲日期和时间的 IBM 标准格式，格式:dd.mm.yyyy      ISO 使用国际标准组织（ISO）制订的日期和时间格式，格式:yyyy-mm-dd     

47、JIS 使用日本工业标准的日期和时间格式,格式为:yyyy-mm-dd      LOC 使用与数据库地区代码相匹配的本地日期和时间格式，本地日期格式，在char(date-type,keywords)中使用LOCATE      USA 使用美国日期和时间的 IBM 标准格式，格式:mm/dd/yyyy  values sysibm.char(223.345,',') ;     结果:223,345  values

48、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.2

49、2.03.906000  values sysibm.char(34);               结果:34to_char 将timestamp类型参数转成指定格式字符串  定义: varchar : sysibm.to_char(<timestamp>,<varchar>)  实例: values sysibm.to_char(current timestamp,'yyyy-mm-dd h

50、h:mi:ss'); 结果:2012-05-10 01:24:435 其它函数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(<

51、;any-type>,<any-type>)      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(<decimal-type:exp1>,

52、<integer:exp2>)       其中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 340translate 字符串转换  varchar : sysibm.translate(<varchar:source-st

53、ring>,<varchar:to-string>,<varchar:from-string>,varchar:pad-string)      按自左到右顺序，按位找from-string中的字符在source-string出现时使用同位置(与from-string中字符相同位置)的字符替换，      如果在from-string中的位置在to-string中没有，则使用pad-string字符串(默认是空格)替换  values sysibm.tra

54、nslate('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.trans

55、late('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

56、(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) ,      &

57、#160;                                                 &

58、#160;              ('NO02','语文',96),('NO02','数学',95) AS t(stno,subname,score)GROUP BY stno;结果：STNO       INFO-    -NO01       语文

59、 90,数学 99,英语 98NO02       语文 96,数学 95xmlagg 函数SELECT stno,REPLACE(REPLACE(XML2CLOB(XMLAGG(XMLELEMENT(NAME stno,subname|' '|score|' , '),'<STNO>'),'</STNO>') info  FROM (VALUES ('NO01','语文',90) , ('NO01

60、','数学',99) , ('NO01','英语',98) ,                                        &

61、#160;                              ('NO02','语文',96),('NO02','数学',95) AS t(stno,subname,score)GROUP BY stno;结果：STNO 

62、60;     INFO-    -NO01       语文 90 , 数学 99 , 英语 98 ,NO02       语文 96 , 数学 95 ,type_id、type_name和type_schema 返回数据类型的id、name和schema，要求参数是structured data typestrip 功能增加的trim标量函数，它可以用来去除表达式的前缀和后缀  &#

63、160;         特殊应用：-获取当前月份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

64、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 ;-获取本月最后

65、一天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(cur

66、rent 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 intRETURN (days(t1) - days(t2) * 86400 +  (midnight_seconds(t1) - midnight_seconds(t2);调用: VALUES second_diff(timestamp('2012-05-10 12:50:23&#