oracle 除以10000,oracle的函数

select upper('ccc') from dual;

select lower('CCC') from dual;

第一个字母大写

select initcap('ggg') from dual;

连接字符串

select concat(concat(first_name,' '),last_name) from employees;

select first_name||' '||last_name from employees;

截取字符串ya

select substr('guoyafei',4,2) from dual;

返回字符串长度

select length('gggg') from dual;

查询字符串位置 返回c2在c1中的位置

c1为原字符串 c2为用要查找的字符串   从第n1个字符开始查 第n2个匹配的

select instr('guoyafeiyafeiyafei','ya',5,2) from dual;

INSTR('GUOYAFEIYAFEIYAFEI','YA',5,2)

------------------------------------

14

不写n1 n2 默认从头开始 第一个匹配的

select instr('guoyafeiyafeiyafei','ya') from dual;

INSTR('GUOYAFEIYAFEIYAFEI','YA')

--------------------------------

4

查询字符串

lpad(c1，n,c2)返回长度为n的字符串

如果n《c1 从左至右截取制定长度的字符串

如果n》c1  c2 is null 空格是从左至右补充c1字符长度

如果n》c1  c2 不为空 c2是从左至右补充c1字符长度

select lpad('guoyf',10),lpad('guoyf',10,'l') ,lpad('guoyf',3,'l') from dual;

LPAD('GUOY LPAD('GUOY LPA

---------- ---------- ---

guoyf lllllguoyf guo

create table aa(str varchar2(2000));

insert into aa select lpad('shiwei',2000,'l') from dual;

insert into aa values(lpad('shiwei',2000,'l'));

rpad(c1，n,c2)返回长度为n的字符串

如果n《c1 从左至右截取制定长度的字符串

如果n》c1  c2 is null 空格是从右至左补充c1字符长度

如果n》c1  c2 不为空 c2是从右至左补充c1字符长度

select rpad('guoyf',10),rpad('guoyf',10,'l') ,rpad('guoyf',3,'l') from dual;

RPAD('GUOY RPAD('GUOY RPA

---------- ---------- ---

guoyf       guoyflllll guo

修剪字符串 不指定修剪的字符串则修剪空格

both 修剪两边

leading 修剪头

trailing 修剪尾

select trim(both 'g' from 'guoyafeig'),trim(leading 'g' from 'guoyafeig'),trim(trailing 'g' from 'guoyafeig') from dual;

TRIM(BO TRIM(LEA TRIM(TRA

------- -------- --------

uoyafei uoyafeig guoyafei

select trim(both  from '   guoyafeig   ')from dual;

TRIM(BOTH

---------

guoyafeig

用shiwei替换first_name 中的teven

SQL> select replace(first_name,'teven','shiwei') from employees where first_name='Steven';

REPLACE(FIRST_NAME,'TEVEN','SHIWEI')

--------------------------------------------------------------------------------

Sshiwei

Sshiwei

SQL> select replace(first_name,'teven') from employees where first_name='Steven';

REPLACE(FIRST_NAME,'

--------------------

S

S

数值函数

round四舍五入

trunc 截断函数 不四舍五入

select round(23.56),round(23.35),round(23.45,1),round(23.45,-1),round(25.45,-1) from dual;

ROUND(23.56) ROUND(23.35) ROUND(23.45,1) ROUND(23.45,-1) ROUND(25.45,-1)

------------ ------------ -------------- --------------- ---------------

24           23        23.5          20          30

SQL> select round(588,-2) from dual;

ROUND(588,-2)

-------------

600

select trunc(56.67),trunc(56.67,1),trunc(56.67,-1) from dual;

TRUNC(56.67) TRUNC(56.67,1) TRUNC(56.67,-1)

------------ -------------- ---------------

56           56.6         50

求余

select mod(5,2) ,mod(8,4),mod(11,-4),mod(-11,4),mod(-11,-4) from dual;

MOD(5,2)   MOD(8,4) MOD(11,-4) MOD(-11,4) MOD(-11,-4)

---------- ---------- ---------- ---------- -----------

1        0           3     -3         -3

日期函数

NEXT_DAY(date,n)

从date时间点开始，下一個星期n的日期

星期日 = 1  星期一 = 2  星期二 = 3

星期三 = 4  星期四 = 5  星期五 = 6  星期六 = 7

select sysdate ,next_day(sysdate,1) from dual;

SYSDATE    NEXT_DAY(S

---------- ----------

2014-04-06 2014-04-13

转化函数

to_char()

to_date()

to_number()

to_timestamp()

nvl(n1,n2) 如果n1不为空返回n1，为空就返回n2

select last_name, nvl(to_char(commission_pct),'ccc') from employees where last_name like 'B%';

LAST_NAME          NVL(TO_CHAR(COMMISSION_PCT),'CCC')

------------------------- ----------------------------------------

Baer              ccc

Baida              ccc

Banda              .1

Bates              .15

Bell              ccc

Bernstein          .25

Bissot              ccc

Bloom              .2

Bull              ccc

nvl2(n1,n2,n3)c1不为空， 则返回c2 ，如果c1为空， 则返回c3

select last_name,salary,nvl2(commission_pct,salary+salary*commission_pct,salary) income from  employees where last_name like 'B%';

LAST_NAME              SALARY     INCOME

------------------------- ---------- ----------

Baer                   10000      10000

Baida                2900       2900

Banda                6200       6820

Bates                7300       8395

Bell                4000       4000

Bernstein            9500      11875

Bissot                3300       3300

Bloom                   10000      12000

Bull                4100       4100

case....end语句"tz_salary" 别名

select last_name,job_id,salary, case job_id

when 'it_prog' then 1.10*salary

when 'st_clerk' then 1.15*salary

when 'sa_rep' then 1.20*salary

else salary

end "tz_salary" from employees;

select last_name,case

when salary > 10000 then 9000

when salary >3000 then salary

else 3000

end "tz_salary" from employees;

decode语句 可与case...end语句转换

decode(expr,s1,h1,s2,h2,s3,h3,.....,default)

select last_name,job_id,salary,

decode('job_id','it_prog',1.10*salary,'st_clerk',1.15*salary,'sa_rep',1.20*salary,salary ) "tz_salary"

from employees;