--##字符函数,大小写处理函数
SELECT LOWER('ABcDe')
FROM dual; -- abcde，全部转换为小写
SELECT UPPER('ABcDe')
FROM dual; -- ABCDE，全部转换为大写
SELECT INITCAP('aBcDe')
FROM dual; -- Abcde，首字母大写
--##字符函数,字符串处理函数
SELECT SUBSTR('ABcDe',2,2)
FROM dual; -- Bc，截取字符串
SELECT SUBSTR('ABcDe',2)
FROM dual; -- BcDe，截取字符串
SELECT SUBSTR('ABcDe',-3,2)
FROM dual; -- cD，截取字符串
SELECT INSTR('ABcDefgdDh','D',3,2)
FROM dual; -- 9，从第三个字符串开始查询第二个字符D的位置
SELECT INSTR('ABcDefgdDh','D')
FROM dual; -- 4，从第一个字符串开始查询第一个字符D的位置
SELECT LPAD('ABcDe',10,'D')
FROM dual; -- DDDDDABcDe，左侧不足补字符串D
SELECT LPAD('ABcDe',3,'D')
FROM dual; -- ABc
SELECT RPAD('ABcDe',10,'D')
FROM dual; -- ABcDeDDDDD，右侧不足补字符串D
SELECT RPAD('ABcDe',3,'D')
FROM dual; -- ABc
SELECT REPLACE('ABcDeEe','e',8)
FROM dual; --ABcD8E8，替换字符
SELECT LENGTH('ABcDe')
FROM dual; -- 5，计算字符串长度
SELECT CONCAT('Hello','World')
FROM dual; -- HelloWorld，连接字符串
SELECT TRIM('H' FROM 'HelloWorld')
FROM dual; --elloWorld，去除首字母
SELECT TRIM('d' FROM 'HelloWorld')
FROM dual; --HelloWorl，去除尾字母
SELECT TRIM('' FROM 'HelloWorld')
FROM dual; --，隐藏字符
--数字函数
SELECT ROUND(245.1234) FROM dual; --245
SELECT ROUND(245.1234,2) FROM dual; --245.12
SELECT ROUND(245.6234,0) FROM dual; --246
SELECT ROUND(245.1234,-2) FROM dual; --200
SELECT ROUND(255.1234,-2) FROM dual; --300
SELECT ROUND(-245.1234,2) FROM dual; -- -245.12
SELECT ROUND(-245.6234,0) FROM dual; -- -246
SELECT ROUND(-245.1234,-2) FROM dual; -- -200
SELECT ROUND(-255.1234,-2) FROM dual; -- -300
SELECT TRUNC(245.1234,2) FROM dual; -- 245.12
SELECT TRUNC(245.6234,0) FROM dual; -- 245
SELECT TRUNC(245.1234,-2) FROM dual; -- 200
SELECT TRUNC(255.1234,-2) FROM dual; -- 200
SELECT MOD(1600,300) FROM dual;
-- 100
SELECT MOD(1500,300) FROM dual; -- 0
--日期函数
SELECT SYSDATE
FROM dual; -- 2012-05-12 12:50:18 ，当前日期
SELECT MONTHS_BETWEEN('15-4月-2012','01-1月-2012')
FROM dual; -- 3.45161290322581，两个日期之间的月数
SELECT ADD_MONTHS(SYSDATE,2)
FROM dual; --2012-07-12 14:23:32，添加月份到日期字段中
SELECT NEXT_DAY('15-4月-2012',3)
FROM dual; --2012-04-17 ，添加天数到日期字段中
SELECT NEXT_DAY(SYSDATE,'星期一')
FROM dual;
SELECT LAST_DAY(SYSDATE)
FROM dual; --2012-05-31 14:25:22，本月份的最后一天
SELECT ROUND(SYSDATE,'month')
FROM dual; --2012-05-01，月份四舍五入
SELECT ROUND(SYSDATE)
FROM dual; --2012-05-13
SELECT TRUNC(SYSDATE,'month')
FROM dual; --月份截取
SELECT TRUNC(SYSDATE,'day')
FROM dual;
--转化函数
SELECT '123' + 123 FROM dual; --246，字符串隐式转化
SELECT TO_CHAR(123456.6543,'L099,999.00') AS "Number Format"
FROM dual; --若是常量数字，需在前加0
SELECT TO_CHAR(123456.6543,'$099,999.00') AS "Number Format"
FROM dual;
SELECT TO_CHAR(SYSDATE,'DAY DD-MON-YYYY') AS "Data Format"
FROM dual; --星期二 15-5月 -2012
SELECT TO_DATE('12-5-2012','DD-MM-YYYY')
FROM dual; --格式字符串必须有。没有意义？
SELECT TO_NUMBER('5432.1234')
FROM dual;
SELECT TO_NUMBER('15,155,100.56','999,999,999.00')
FROM dual; --变量字符串与格式字符串的格式必须一致
--常规函数
SELECT NVL(NULL,0) FROM dual; -- 0
SELECT NVL(NULL,'N/A') FROM dual; -- N/A
SELECT NVL(123,0) FROM dual; -- 123
SELECT NVL('学生','N/A') FROM dual; -- 学生
SELECT NVL2(NULL,0,1) FROM dual; -- 1
SELECT NVL2(123,0,1) FROM dual; -- 0
SELECT NULLIF(1,1) FROM dual; -- NULL
SELECT NULLIF(2,1) FROM dual; -- 2
SELECT NULLIF('好','不好') FROM dual; -- 好
SELECT COALESCE(NULL,NULL,'不好','好') FROM dual; -- 不好，参数必须为同一数据类型
--DECODE示例
DECODE
SELECT last_n