ere instr(ename,'A')=1;
ENAME
----------
ALLEN
ADAMS
SQL> select ename,instr(ename,'LL') from emp;
ENAME INSTR(ENAME,'LL')
---------- -----------------
SMITH 0
ALLEN 2
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
rpad右边填充字符
SQL> select rpad(deptno,10,' ') deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
lpad左边填充
col dname just right 行标题对齐方式
trim(both|leading|trailing 'char' from expr)
SQL> select trim('A' from 'AAABBBAAA') t from dual;
T
---
BBB
ltrim(expr,'char'),rtirm 版本大于等于10g才会有的
replace(expr,old,new);替换字符
chr(10)换行符 官方文档sql第五章
数字函数
round() 近似值
trunc() 截取
mod() 取余
abs() 绝对值
ceil()取整
4 5 6 . 7 8 9
-3 -2 -1 0 1 2 3
SQL> select round(456.789,2),trunc(456.789,2)from dual;
ROUND(456.789,2) TRUNC(456.789,2)
---------------- ----------------
456.79 456.78
SQL> c/2/-1
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> l
1* select round(456.789,-1),trunc(456.789,2)from dual
SQL> /
ROUND(456.789,-1) TRUNC(456.789,2)
----------------- ----------------
460 456.78
日期函数
default value DD-MON-RR
****0-49,50-99 加减一
current last 2 number ---<50
last 2 number ---0-49<50
50-99 -1 世纪
--->=50
>=50
<50 0-49 +1
SQL> select sysdate from dual;
SYSDATE
---------
19-JUL-14
2014 14
2014 14 2014
**SQL> show parameter nls_date_format;
**SQL> alter session set nls_date_format='yyyy-mm-dd'
日期可以进行算数运算，也可以进行round,trunc运算，trunc(sysdate,'day')
months_between
SQL> select ename,months_between(sysdate,hiredate) s from emp;
ENAME S
---------- ----------
SMITH 403.07913
ALLEN 400.982356
WARD 400.91784
JONES 399.563001
MARTIN 393.724291
BLAKE 398.595259
CLARK 397.337195
SCOTT 327
KING 392.07913
TURNER 394.369453
ADAMS 325.885582
add_months()
next_day()
last_day()
***注意日期的格式***
转换函数
=显式转化
to_char(date,'format_model')
SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> l
1* select to_char(hiredate,'yyyy-mm-dd')from emp
SQL> c/yyyy/fmyyyy
1* select to_char(hiredate,'fmyyyy-mm-dd')from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1987-4-19
1981-11-17
1981-9-8
1987-5-23
SQL> c/dd/fmdd
1* select to_char(hiredate,'fmyyyy-mm-fmdd')from emp
SQL> /
TO_CHAR(HI
----------
1980-12-17
1981-2-20
1981-2-22
1981-4-02
1981-9-28
1981-5-01
1981-6-09
1987-4-19
1981-11-17
1981-9-08
1987-5-23
9 0 $ L . ,特殊符号
SQL> select ename,to_char(sal,'9,999.00') sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
SQL> select ename,to_char(sal,'9G999D00') sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SQL> select ename,to_char(sal,'$9G999D00') sal from emp;
ENAME SAL
---------- ----------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
SQL> select to_char(16,'x') from dual;
TO
--
##
to_numbe |