[02] Oracle简单单行函数(字符+数值+日期+转换+通用)

1. 字符函数

--upper(str), lower(str)：将str转换成大，小写
select upper('hello') as x1, lower('HELlo') as x2 from dual;
--initcap(str)：将str中的每个单词的首字母大写，其他字母小写
select initcap('hELlo world!') as x from dual;
--concat(str1, str2)或(str1 || str2)：将str1和str2两个字符串连接成一个字符串
select concat(1 , 'b') as x1, 1.2 || 'b' as x2 from dual;
--length(str)：计算str的长度
select length('abcde') as x from dual;
--replace(str, oldSubStr, newSubStr)：将str中的字符串oldSubStr替换成newSubStr
select replace('abceda', 'abc', '*') as x from dual;
--lpad(str, lengthAffterPadding, paddingStr)：如果length(str)>lengthAffterPadding, 则会截掉右侧的字符串，
--如果length(str)<lengthAffterPadding, 则会在str的左侧用paddingStr进行填充
select lpad('smith111111111', 10, '*') as longStr,  lpad('smith', 10, '*1') as shotStr from dual;
--lpad(str, lengthAffterPadding, paddingStr)：如果length(str)>lengthAffterPadding, 则会截掉右侧的字符串，
--如果length(str)<lengthAffterPadding, 则会在str的右侧用paddingStr进行填充
select rpad('smith111111111', 10, '*') as longStr,  rpad('smith', 10, '*1') as shotStr from dual;
--ascii(character)：返回与character字符对应的十进制数
select ascii('A') A,ascii('a') a,ascii('12') 一打,ascii(' ') kg from dual; 
--chr(num):返回与num十进制数对应的字符
select chr(65) A,chr(122) z from dual;
--chr(10)：换行，在command中可以显示出来
select '1' || chr(10) || '22' || chr(10) || '333' from dual;
--trim(str)：过滤掉str首尾空格字符，ltrim过滤掉左边的，rtrim过滤掉右边的
select trim(' smith    ') as delBoth, ltrim(' smith    ') as delL, rtrim(' smith    ') as delR from dual;
--instr(str1, str2, startPos, nthAppear)：从str1的startPos处开始查找str2的第nthAppear出现的索引（字符串索引从1开始）
select instr('Hello World','or', 7, 1) as res from dual;
--substr(str, start, length)：从str的start处开始向右截取length个字符串，没有length值则截取到最后，如果start负，则start是从右到左数start个
select substr('abced', 2, 3) as positivePos, substr('abced', -3, 3) as negativePos from dual;

2. 数值函数

--round(num, digits)：将num进行四舍五入，保留digits位小数，如果digits为负数，则对小数点左侧前digitis位进行四舍五入
select round(412.313, 2) as affterDots, round(4521, -2) as beforeDots from dual;
--trunc(num, digits)：截取，与round相似，但是不对指定小数前或后的位数进行四舍五入处理
select trunc(412.313, 2) as affterDots, trunc(4521, -2) as beforeDots from dual;
--mod(num, dividend)：num%dividend取余，结果的符号与num的符号一直，dividend除数为0的话结果等于num
select mod(3, -2) as positiveNum, mod(-3, -2) as negativeNum, mod(-3, 0) as dividendZero from dual;

3. 日期函数

--返回当前日期
select sysdate from dual;
--返回当前星期第一天
select trunc(sysdate,'dd') from dual;
--返回当月第一天
select trunc(sysdate,'mm') from dual;
--返回当月最后一天
select trunc(last_day(sysdate)) from dual;
--获取当月的天数
select cast(to_char(last_day (sysdate), 'dd') as int) daysOfMonth from dual;
--获取当月剩余天数
select sysdate, last_day (sysdate) "Last day", last_day (sysdate) - sysdate "Days left" from dual;
--系统日期所在月份的倒数第3天
select last_day(sysdate)-2 from dual;
--下个月的今天
select add_months(sysdate,1) from dual;
--返回当年第一天'y'、'yyyy'
select trunc(sysdate,'yyyy') from dual;
--返回一年的最后一天
select add_months(trunc(sysdate,'y'), 12) - 1 from dual
--返回一年的天数（去年的第一天-今年的第一天就是今年的天数）
select add_months(trunc(sysdate,'y'), 12) - trunc(sysdate,'y') from dual;
--从系统日期开始的第一个星期一
select next_day(sysdate,'星期一') from dual;
--months_between(startMonth, endMonth)：返回startMonth与endMonth日期之间相隔的月数，若startMonth比endMonth小，则返回一个负数
select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100128', 'yyyymmdd')) as months from dual;
--当前时间减去7秒的时间
select sysdate,sysdate - interval '7' second from dual;
--当前时间减去7分的时间
select sysdate,sysdate - interval '7' minute from dual;
--当前时间减去7小时的时间
select sysdate,sysdate - interval '7' hour from dual;
--当前时间减去7天的时间 
select sysdate,sysdate - interval '7' day from dual;
--当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual;
--当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual;

4. 转换函数

--格式化日期-输出年分
select to_char(sysdate,'yyyy') from dual;
--格式化日期-输出年月日
select to_char(sysdate,'fmyyyy-mm-dd') from dual;
--格式化日期-输出年月日时分秒
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') from dual t;
--将字符串转换成指定格式的日期
Select to_date('20090210','yyyyMMdd') from dual;
--将整数转换成指定格式的日期
select to_date(to_char(20141201), 'YYYYMMDD') from dual;
--将字符串转换成指定格式的日期
select to_date('20140630 18:28:20','YYYY/MM/DD HH24:MI:SS') from dual t;
--Oracle中取毫秒级时间(获取的时间是精确到毫秒以后三位的)
select to_char(current_timestamp,'yyyy/mm/dd/hh24:mi:ss:ff') from dual  
--格式化-输出美元符号
select to_char(10000000000,'$999,999,999,99') from dual;-- $100,000,000,00
--格式化-输出人民币符号
select to_char(1000000,'L99,999,99') from dual;--输出 ￥10,000,00
--将字符串转换成数字
select to_number('13.2')+to_number('14') from dual;
--保留三位有效数字
select trunc(to_number('1000000.123'),3) from dual;

5. 通用函数

--nvl(isnull, default)：如果isnull为空则返回default，否则返回isnull
select nvl(null, -1) as resDefault, nvl(2, -1) as resIsNull from dual;
--nullif(exp1, exp2), 如果exp1和exp2相等则返回null，否则返回exp1
select nullif(1, 1) as equalRet, nullif(1, 21) as notEqualRet from dual;
--nvl2(num, notNullRet, nullRet)：如果num不为null则返回notNullRet，如果num为null则返回nullRet
select nvl2(1, 1, 2) as notNullRet, nvl2(null, 1, 2) as nullRet from dual;
--coalesce(num1, num2, num3...)：返回第一个非null值
select coalesce(null, null, 1) as x from dual;
--decode(num, num1, num1Ret, num2, num2Ret, defaultValue)：和case 表达式类似，decode()函数也用于实现多路分支结构
--判断num的值，如果等于num1则返回num1Ret，如果等于num2则返回num2Ret，都不等于则返回defaultValue
select decode(1, 1, 'hi', 2, 'hello', 'defaultValue') from dual;