DB2里那些常用函数到底有哪些，怎么用才算对呢？

说到DB2数据库，里面确实有非常多好用的函数，能帮我们轻松处理数据，但要用对它们，避免踩坑，就得了解清楚它们的脾气秉性，下面我就把一些最常见的函数分门别类地讲一讲,重点说说怎么用才算对。

处理字符串的函数

字符串是我们打交道最多的数据类型之一。

• CONCAT 函数和 || 操作符

  • 干嘛用的：把两个或多个字符串拼接到一起,比如把姓和名拼接成完整的姓名。
  • 怎么用：有两种方法。
    1. 用函数：CONCAT('Hello', 'World') 结果是 HelloWorld，注意，DB2的CONCAT一次只能连接两个字符串，要连接多个就得嵌套，CONCAT(CONCAT('A', 'B'), 'C')。
    2. 用操作符：更推荐这个，简单直接。'Hello' || ' ' || 'World' 结果是 Hello World，这个符号就是键盘上的竖线,按两下。
  • 怎么用对：要特别注意NULL值，如果拼接的任何一个字符串是NULL，整个结果就会变成NULL，这经常是导致数据莫名其妙丢失的原因，解决办法是用COALESCE函数（后面会讲到）把NULL转成空字符串，COALESCE(可能为NULL的字段, '') || '其他部分'。

• SUBSTR 函数

  • 干嘛用的：从字符串里截取一部分。
  • 怎么用：SUBSTR(字符串, 开始位置, 要截取的长度)。SUBSTR('ABCDEFG', 2, 3) 是从第2个字符‘B’开始，截取3个字符，结果是BCD，这里要注意，DB2的字符串位置是从1开始数的,不是0。
  • 怎么用对：确保“开始位置”不要超过字符串的总长度，否则会得到空字符串，要截取的长度”省略了,就会一直截取到字符串末尾。

• TRIM, LTRIM, RTRIM 函数

  • 干嘛用的：去掉字符串首尾的空格。TRIM是两头都去，LTRIM只去左边的，RTRIM只去右边的。
  • 怎么用：TRIM(' 你好 ') 结果是你好，它也可以指定去掉其他字符，TRIM('X' FROM 'XXHelloXX') 结果是Hello。
  • 怎么用对：在比较用户输入或外部导入的数据时，先用TRIM处理一下是个非常好的习惯,能避免因为头尾空格导致查不出数据的问题。

处理数字的函数

• ROUND, CEILING, FLOOR 函数

  • 干嘛用的：对小数进行四舍五入或取整。
  • 怎么用：
    • ROUND(数字, 保留小数位数)：四舍五入。ROUND(123.4567, 2) 结果是46。
    • CEILING(数字)：向上取整，返回大于等于这个数的最小整数。CEILING(123.1) 结果是124。
    • FLOOR(数字)：向下取整，返回小于等于这个数的最大整数。FLOOR(123.9) 结果是123。
  • 怎么用对：区分清楚你的业务场景是需要四舍五入还是强制向上/向下取整,比如计算运费可能就需要向上取整。

• COALESCE 函数（也常用于处理NULL）

  • 干嘛用的：从参数列表里返回第一个非NULL的值，它虽然不完全是数字函数,但处理数字时极其重要。
  • 怎么用：COALESCE(字段1, 字段2, 0)，意思是，如果字段1不是NULL，就返回字段1的值；如果字段1是NULL，就看字段2；如果字段2也是NULL,就返回0。
  • 怎么用对：这是对付NULL值的“万能钥匙”，在计算总和、平均值时，如果字段可能为NULL，用COALESCE(字段, 0)先转换一下,可以避免整个计算结果变成NULL。

处理日期的函数

日期和时间处理是数据库查询的常客,也容易出错。

• CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP

  • 干嘛用的：获取数据库服务器当前的日期、时间、时间戳。
  • 怎么用：它们是DB2特有的“特殊寄存器”，直接写在SQL里就行。SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1,时间戳包含了日期和精确的时间。
  • 怎么用对：注意这些值取的是数据库服务器的时间，不是你本地电脑的时间,在分布式系统里要特别留意服务器所在的时区。

• YEAR, MONTH, DAY 函数

  • 干嘛用的：从一个日期或时间戳里提取出年、月、日的数字部分。
  • 怎么用：YEAR(CURRENT DATE) 会返回2024（假设今年是2024年）。
  • 怎么用对：常用于按年、月进行分组统计。GROUP BY YEAR(订单日期), MONTH(订单日期)。

• 日期计算：DAYS 函数和算术运算

  • 干嘛用的：计算两个日期之间的天数差,或者对一个日期进行加减。
  • 怎么用：
    • 计算天数差：DAYS(日期1) - DAYS(日期2)。
    • 日期加减：日期可以加上或减去一个数字，这个数字代表天数。CURRENT DATE + 10 表示10天后的日期。
  • 怎么用对：DB2对日期格式很严格，直接写日期字符串要用DATE()函数转换，DATE('2024-05-27')，否则可能会报错或结果不对,这是新手最容易踩的坑之一。

条件判断函数

• CASE 表达式
  • 干嘛用的：实现类似“....否则...”的逻辑。
  • 怎么用：有两种形式。
    1. 简单CASE：CASE 字段 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ELSE 其他结果 END,适合等值判断。
    2. 搜索CASE：CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 结果3 END，功能更强大,可以进行各种复杂的条件判断。
  • 怎么用对：一定要用END关键字结束，否则语法错误。ELSE部分是可选的，但最好总是写上，明确指定当所有条件都不满足时的默认值,避免出现意外的NULL。

总结一下怎么用才算对：

1. 时刻警惕NULL：DB2中NULL与任何值运算（除了用IS NULL判断）结果都是NULL，多用COALESCE或CASE处理。
2. 注意数据类型：确保函数参数的类型是正确的，比如不要试图用SUBSTR去截取一个数字。
3. 理解参数含义：比如SUBSTR的起始位置是1，ROUND的精度参数可以是负数（表示舍入到十位、百位等）。
4. 勤加测试：对于不确定的函数，最好在测试环境或临时表上先用简单的例子验证一下结果,再写到复杂的业务SQL里。

这些函数是构建复杂SQL语句的基石，熟练掌握并能避开常见陷阱，就能让你的数据库操作既准确又高效。 参考了IBM官方DB2文档中关于内置函数的说明，并结合了常见的数据库操作实践。）