SQL> select lower('YIFAN'), upper('YiFan'), initcap('YiFan girl')
2 from dual;
lower:将所有字母变成小写
upper:将所有字母变成大写
initcap:仅将单词的首字母大写
SQL> select concat('hello','world'), substr('helloworld',2,4), length('helloworld')
2 from dual;
concat:连接字符串
substr:(字符串,a,b)从第a个字符开始,输出字符长度为b
length:统计字符串长度
SQL> select instr('helloworld','l')from dual;
instr:(字符串,字符)查询字符在字符串里第一次出现的位置
SQL> select employee_id, last_name, lpad(salary,10,'*')
2 from employees;
lpad:(表项,a,字符)将表项的值输出a位,如果不够,在输出值的左端补字符
rpad:同理,在右端补字符
SQL> select trim('h' from 'hhhellohhworldhh') from dual;
SQL> select replace('abcdab','b','m') from dual;
trim:将查找的h字符从字符串中移除,仅移除首尾出现的
replace:用m替换b在字符串中出现的位置,替换字符串中所有b
SQL> select round(435.45,2), round(435.45),round(435.45,-2) from dual;
SQL> select trunc(435.45,2), trunc(435.45),trunc(435.45,-2) from dual;
SQL> select mod (1100,300) from dual;
round:(a,b)输出a保留b位小数,没有b则视为b=0,遵守四舍五入规则
trunc:(a,b)输出a保留b位小数,没有b则视为b=0,不会四舍五入,直接舍去
mod:取余
SQL> select employee_id, last_name, (sysdate - hire_date)/30, months_between(sysdate,hire_date)
2 from employees;
SQL> select add_months(sysdate,2), add_months(sysdate,3), next_day(sysdate,'星期日')
2 from dual;
months_between:两个日期间有多少个月
add_months:为当前日期加上x个月
next_day:下一个星期几是几号