# concat 合并 ——  "+"等同于concat(oracle使用 "||" )
select concat('hello', ' ', 'world') from dual;
# output: hello world
# oracle只允许传入两个参数合并

# substring 截取 —— left right 左截取 右截取（oracle不支持）
select substring('hello world' from 1 for 5)  from dual;
# output: hello
# oracle用法
select substr('hello world', 1, 5) from dual;

# position 位置
select position(' ' in 'hello world') from dual;
# output: 5
# oracle用法：
select instr('hello world', ' ') from dual;

# substring position 联合使用，查找
select substring('hello world' from 1 for position(' ' in 'hello world') - 1) from dual;
# output: hello
# oracle用法：
select substr('hello world' , 1, instr('hello world', ' ') - 1) from dual;

# round 数值格式化
select round(1.1357, 2), round(1357, -2) from dual;
# output: 1.14 1400

# coalesce 替换空值 等同于ifnull
select coalesce('', 'hello world') from dual;
# output: hello world
# oracle也支持coalesce，同时有另一种用法：
select nvl('', 'hello world') from dual;

# replace 替换
select replace('hallo world', 'a', 'e') from dual;

# case when 条件
case when condition1 then result1
     when condition2 then result2
     else result
     end
# oracle用法：
decode(condition1, result1, result2)

# time date
SELECT current_date, current_timestamp FROM dual;
# output: Mon, 12 Apr 2021 00:00:00 GMT Mon, 12 Apr 2021 02:53:35 GMT
# oracle用法：
select sysdate from dual;

# date_formate日期时间格式化
SELECT DATE_FORMAT(current_date,'%d/%m/%Y') FROM dual
# output: 12/04/21
# oracle用法：
select to_char(sysdate, 'dd/mm/yyyy') from dual;

# interval 几天/月/年后(Oracle不支持)
select current_date,
       curent_date + interval 7 day,
       current_date + interval 1 month,
       current_date + interval 1 year,
from dual;

# extract 提取日期时间
SELECT current_date,
       EXTRACT(YEAR FROM current_date)  AS yr,
       EXTRACT(MONTH FROM current_date) AS mnth
  FROM dual

常用函数

• length 长度

• abs 绝对值

• max min 最大值 最小值

• sum 求和

• avg 平均值

• count 计数

• floor 向下取整

• ceil 向上取整

• mod 取余

• trim 去除首尾空格

• nullif(x, y)：x==y则返回空，否则返回x