--1 字符串函数=========================================================================
--ascii 返回字符串首字符的ASCII码值
--integer : sysfun.ascii(<char>|<varchar(4000)>|<clob(1M)>)
values sysfun.ascii('A'),sysfun.ascii('ABC'),sysfun.ascii('中国人')-- --结果：65 65 206
--hex 返回字符的十六进制值
--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); --结果:Y
--concat 字符串连接
--string-type : sysibm.concat(<string-type>,<string-type>)
values sysibm.concat('hello','world');--结果:helloworld
--lower,lcase 转小写，upper,ucase 转大写
--varchar(4000)|clob(1M) : sysfun.lcase(<varchar(4000>|<clob(1M)>)
--char|varchar : sysibm.lower(<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 NAME
--ltrim 去左空格，rtrim 去右空格，trim 去左右空格
--char|varchar|graphic 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 ')||']','['||trim(' h w ')||']'; 结果:[h w ] [ h w] [h w]
--trim 去除表达式前后指定的字符，默认是空格
--character-type : sysibm.trim([B|BOTH|L|LEADING|T|TRAILING] <strip-express> FROM <string-express>) 将表达式前或后(默认是BOTH)指定的字符(strip-express)移除
values trim('0' from '00012300') 结果：123
--strip 去除表达式前后指定的字符，默认是空格
--character-type : sysibm.strip(<string-express>[,B|BOTH|L|LEADING|T|TRAILING,<strip-express>])
values strip('**88000***',b,'*') 结果：88000
--posstr 查找字符串位置(从1开始计数),没有找到返回0
--string-type : sysibm.posstr(<string-type:source-string>,<string-type:search-string>) 前者为source-string，后为search-string
values sysibm.posstr('the one is not that one','one'); 结果:5
--position 查找字符串位置，与posstr功能类似，由于考虑到不同国家的字符集，增强编码模式
--string-type : sysibm.position(<string-type:source-string>,<string-type:search-string>,CODEUNITS16|CODEUNITS32|OCTETS)
values position('a','abca',OCTETS) 结果：1
--locate 搜索子字符串出现的位置，找不到返回0
--integer : sysfun.locate(<varchar(4000):exp1>,<varchar(4000):exp2>[,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(<varchar(4000)>|<clob(1M)>|<blob(1M)>,<integer>)
values sysfun.repeat('ab',5); 结果:ababababab
--space 产生空格
--varchar(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来替换
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.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从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(<string-type:string>,<integer:start>[,<integer:length>],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(<any-builtin-type>)
values octet_length('abc中'); 结果：6
--length 返回字符串的长度(字节)
--integer : sysibm.length(<any-builtin-type>,[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(<numeric-type>)
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 日期函数=================================================================================================================
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:string>,<exp2:string>) ，其中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:integer>,<exp2:char(22)>) 参数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(<exp> as <data-type>)
values cast('123' as integer);
decimal或者dec
decimal : sysibm.dec(<numeric-type>[,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(<varchar>[,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(<decimal-type>[,varchar]) 返回数值的字符串格式，如果指定varchar字符串的话，那么decimal中的点按指定字符串显示
char : sysibm.char(<character-type>[,integer]) 返回字符串，如果指定integer的话就按长度返回
char : char(<date-type>[,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(<timestamp>,<varchar>)
实例: 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(<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>,<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 340
translate 字符串转换
varchar : sysibm.translate(<varchar:source-string>,<varchar:to-string>,<varchar:from-string>[,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)
