select concat('010-',88888888)||'转123' from dual;
- INITCAP返回字符串并将字符串的第一个字母变为大写;
select initcap('smith') from dual;
select ename, length(ename) from emp;
select lower('AaBbCcDd') from dual;
select upper('AaBbCcDd') from dual;
- SUBSTR(string,start,count) 取子字符串,从start开始,取count个
select substr(13088888888,3,8) from dual;
- REPLACE(string,s1,s2) ,string 希望被替换的字符或变量,s1 被替换的字符串,s2 要替换的字符串
select replace('he love you','he','i') from dual;
- MOD(n1,n2) ,返回一个n1除以n2的余数
select mod(10,3),mod(3,3),mod(2,3) from dual;
select round(55.5),round(-55.5) from dual;
select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
select to_char(add_months(to_date(199912,'yyyymm'),2),'yyyymm') from dual;
select to_char(add_months(to_date(199912,'yyyymm'),-2),'yyyymm') from dual;
select last_day(sysdate) from dual;
- MONTHS_BETWEEN(date2,date1) ,date2-date1的月份
select months_between(sysdate, to_date('20170101', 'yyyymmdd')) mon_between from dual;
select to_char(sysdate, 'dd-mm-yyyy') from dual;
select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;
- TO_DATE(string,format)将字符串转化为ORACLE中的一个日期
select to_date('2017-01-01', 'yyyy-mm-dd') from dual;
select to_number('1999') from dual;
- NVL(expr1, expr2)->expr1为NULL,返回expr2;不为NULL,返回expr1。注意两者的类型要一致
- CASE语句
select ename ,case when ename='SMITH' then 0 else 1 end from emp;