Oracle 常用系统函数

2  字符函数

1.    replace( 字符串1,字符串2,字符串3)

   replace( char, search_string, replace_string)

   功能：在“字符串1”中搜索“字符串2”，并将其替换为“字符串3”。

例如下面的命令是将所有员工名字中出现的”A”替换为”中国”。

SQL>selectreplace(ename, ‘A‘, ‘中国‘) from scott.emp;

2.   instr(C1, C2,  I,  J)

功能：在一个字符串中搜索指定的字符，返回发现指定的字符的位置。其中：

C1被搜索的字符串

C2希望搜索的字符串

I  搜索开始位置，默认为1

J  第J次出现，默认为1

例如下面的命令是找出”oracletraning” 第二个ra出现的位置。

SQL>Selectinstr(‘oracle traing‘ , ‘ra‘,1,2) from dual;

3.    ASCII(单个字符)

功能：返回与指定字符对应的十进制数。

SQL>Selectascii (‘A‘) A, ascii(‘a‘) a , ascii (‘ ‘) space from dual;

说明：dual是oracle系统内部提供的一个用于临时数据计算的特殊表，它只有一列DUMMY。

4.    CHR(整数)

功能：给出整数，返回对应的字符。

SQL>Selectchr(54740) zhao, chr(65)  char65  from dual;

5.    CONCAT(字符串1,字符串2)

功能：连接两个字符串。

Selectconcat(‘0532-‘, ‘96656‘) || ‘拨 0‘ 崂山矿泉订水 fromdual;

Selectconcat (ename, ‘是优秀员工‘) from scott.emp;

该函数和|| 的作用是一样的。

6.   INITCAP（字符串）  

功能：返回字符串并将字符串的第一个字母变为大写。

Selectinitcap(‘smith‘) upp from dual;

Selectinitcap(ename) ename from scott.emp;

7.    LENGTH(字符串)  

功能：返回字符串的长度

例如：查询雇员姓名，姓名字符长度，工资及工资数字长度。

Selectename, length(ename), sal, length(to_char(sal)) from scott.emp;

例如：请查询名字的字符长度为4的雇员

Select* from scott.emp where length(ename) =4;

Selectlength(‘李明‘) from dual;  --长度为2，不区分英汉，都占1个字符

说明：

The LENGTH functionsreturn the length of char. LENGTH calculates length usingcharacters as defined by the input character set. 

    --返回以字符为单位的长度.

LENGTHB usesbytes instead of characters. 

    --返回以字节为单位的长度.

LENGTHC usesUnicode complete characters. 

    --返回以Unicode完全字符为单位的长度.

LENGTH2 usesUCS2 code points. 

    --返回以UCS2代码点为单位的长度.

LENGTH4 usesUCS4 code points.

   --返回以UCS4代码点为单位的长度.

下面的例子比较了不同长度计算函数的差异：

Createtable S(a char(5), b nchar(5), c varchar(5), d nvarchar2(5));

insertinto S values(‘aa‘,‘aa‘,‘aa‘,‘aa‘);

insertinto S values(‘你好‘,‘你好‘,‘你好‘,‘你好‘);

insertinto S values(‘你好!‘,‘你好!‘,‘你好!‘,‘你好!‘);

selectlength(a), a, length(b), length(c), length(d) from s;

selectlengthb(a),a,lengthb(b),lengthb(c),lengthb(d)from s;

selectlengthc(a),a,lengthc(b),lengthc(c),lengthc(d)from s;

8.   LOWER（字符串）

功能：返回字符串，并将所有的字符小写。

Selectlower(‘AbBbCcDd‘) AbBbCcDd from dual;

9.   UPPER（字符串）

功能：返回字符串，并将所有的字符大写。

Selectupper(‘AbBbCcDd‘) AbBbCcDd from dual;

10.  SUBSTR(string,start, count)  

功能：取子字符串，从start开始，取count个。

Selectsubstr(‘13370840627‘,3,5) from dual;

例如：请把雇员名字首字母小写，其他字母大写。

Selectlower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from scott.emp;

11.  RPAD和LPAD函数

功能：在列的右/左边粘贴字符

例如：显示Page1要占15个字符，不足的部分左/右边用*.占位。

Selectlpad(‘Page 1‘,15, ‘*.‘) "LPAD example " from dual;

SelectRpad(‘Page 1‘,15, ‘*.‘) "RPAD example " from dual;

12.  LTRIM和RTRIM 

功能：删除左边/右边出现的字符串

举例如下：

Selectltrim(‘Qingdao University‘, ‘Q ‘) from dual;

13.  SOUNDEX

功能：返回一个与给定字符串读音相同的字符串

Create  table table1(xm varchar(8) );

Insert intotable1 values(‘weather‘);

Insert intotable1 values(‘wether‘);

Insert intotable1 values(‘goose‘)

Select xmfrom table1 where soundex(xm)=soundex(‘weather‘);

14.  TRIM(‘s’from ‘string‘) 

功能：去掉指定字符串前后的某些字符。

例如：

Selecttrim(0 from 0098123400) "TRIM example " from dual;

15.  To_char(datetime,string format)

功能：将日期型转换为字符串。

To_char(number,stringformat)

功能：将数值转换为字符串

例如：

Selectto_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss‘) from dual;

Selectename,to_char(sal, ‘L99G999D99‘) from scott.emp;

说明：

9：显示数字，并忽略前面0

0：显示数字，如位数不足，则用0补齐

.：在指定位置显示小数点

,：在指定位置显示逗号

$：在数字前加美元

L：在数字前加本地货币符号

C：在数字前加国际货币符号

G：在指定位置显示组分隔符

D：在指定位置显示小数点符号

16.  to_number(string)

功能：将给出的字符串转换为数字。

例如：

Selectto_number(‘1999‘) year from dual;

17.  decode函数

功能：相当于一条if语句

举例：

create tablestudent(sno char(2), sex char(1),birthday date)-- 创建学生表

insert intostudent values(‘01‘,‘M‘,‘18-8月-1992‘); --添加记录

insert intostudent values(‘02‘,‘T‘, ‘9-5月-1993‘); --添加记录

insert intostudent values(‘03‘,‘F‘ ,‘18-1月-1994‘); --添加记录

insert intostudent values(‘04‘,null,‘11-8月-1993‘); --添加记录

select *from student;  -- 查询学生表

    /*查询学生的学号和性别信息，如果性别值为M则显示male，性别值为F则显示female，如果为空值则显示unknow，否则则显示invalid*/

selectsno,sex,decode(sex, ‘M‘,‘male‘,‘F‘,‘female‘,null,‘unknow‘,‘invalid‘)fromstudent;

举例：查询student表，统计1992、1993、1994各年出生的学生人数。

selectto_char(trunc(birthday,‘year‘),‘yyyy‘),count(*)

from student

whereto_char(trunc(birthday,‘year‘),‘yyyy‘) in (‘1994‘,‘1993‘,‘1992‘)

group byto_char(trunc(birthday,‘year‘),‘yyyy‘)

在SQL疑难问题中，decode函数常常发挥非常灵活的作用。其中一个就是为了某种目的将一个表的行转换成列。例如：

selectsum(decode(to_char(trunc(birthday,‘year‘),‘yyyy‘),‘1994‘,1,0) ) birth_1994,

sum(decode(to_char(trunc(birthday,‘year‘),‘yyyy‘),‘1993‘,1,0)) birth_1993,

sum(decode(to_char(trunc(birthday,‘year‘),‘yyyy‘),‘1992‘,1,0)) birth_1992

from student

whereto_char(trunc(birthday,‘year‘),‘yyyy‘) in (‘1994‘,‘1993‘,‘1992‘)

18.  greatest函数

功能：返回一组表达式中的最大值，即比较字符的编码大小。

举例：

selectgreatest(‘AD‘,‘AC‘,‘AA‘) from dual;

19.  least函数

功能：返回一组表达式中的最小值，即比较字符的编码大小。

举例：

selectleast(‘AD‘,‘AC‘,‘AA‘) from dual;

20.  uid  函数

功能：返回标识当前用户的唯一整数

举例：

show user

select uidfrom dual;

selectusername,user_id from dba_users where user_id=uid;

2  数字函数

1.   ABS

功能：取绝对值

Selectabs(100),abs(-100) from dual;

2.   ACOS

功能：给出反余弦的值

Selectacos(-1) from dual;

3.   ASIN

功能：给出反正弦的值

Selectasin(0.5) from dual;

4.   ATAN

功能：返回一个数字的反正切值

Selectatan(1) from dual

5.   CEIL 

功能：返回大于或等于给出数字的最小整数

Selectceil(3.1415926) from dual;

6.   COS 

功能：返回一个给定数字的余弦

Selectcos(-3.14) from dual;

7.   EXP

功能：返回一个数字e的n次方

Selectexp(2),exp(1) from dual;

8.   FLOOR

功能：对给定的数字取整数（舍掉小数位）

Selectfloor(234.56) from dual;

9.   LN

功能：返回一个数字的对数值

Selectln(1), ln(2) from dual;

10.  Log(n1,n2) 

功能：返回以n1为底的n2的对数

Selectlog(2,1), log(2,4) from dual;

11.  MOD(n1,n2) 

功能：返回一个n1除以n2的余数

Selectmod(10,3) mod(2,3) from dual;

12.  POWER(n1,n2)

功能：返回n1的n2次方

Selectpower(2,5) from dual;

13.  ROUND

功能：按照指定的精度进行舍入（四舍五入）

14.  TRUNC

功能：按照指定的精度进行舍入（用于截取，没有指定截取到第几位，默认取整数）。

举例：

Selectround(55.5),trunc(55.5),round(-55.5), trunc (-55.5)from dual;

Selecttrunc(12.345,2), trunk(12.234,-2) from dual;

Selectto_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh, to_char(trunc(sysdate,’mi’), ’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;

selecttrunc(sysdate) from dual --2014-3-18今天的日期为2014-3-18
select trunc(sysdate, ‘mm‘) from dual --2014-3-1返回当月第一天.
select trunc(sysdate,‘yy‘) from dual--2014-1-1 返回当年第一天
select trunc(sysdate,‘dd‘) from dual--2014-3-18 返回当前年月日
select trunc(sysdate,‘yyyy‘) from dual--2014-1-1 返回当年第一天
select trunc(sysdate,‘d‘) from dual--2014-3-16 (星期天)返回当前星期的第一天
select trunc(sysdate, ‘hh‘) from dual --2014-3-1814:00:00 当前时间为14:41
select trunc(sysdate, ‘mi‘) from dual --2014-3-1814:41:00 TRUNC()函数没有秒的精确

15.  SIGN

功能：取数字n的符号，大于0返回1，小于0返回-1，等于0返回0

Select sign(12),sign(-10),sign(0)from dual;

16.  SQRT(n)

功能：返回数字n的平方根

Selectsqrt(64) ,sqrt(10)from dual;

17.  avg(distinct| all) 

功能：返回平均值

Selectavg(sal) from scott.emp;

selectavg(sal) from emp;

18.  max(distinct| all)

功能：返回最大值

Selectmax(sal) from scott.emp;

19.  min(distinct| all)

功能：返回最小值

Selectmin (sal) from scott.emp;

20.  stddev(distinct| all)

功能：求标准差

Selectstddev (sal) from scott.emp;

21.  variance(distinct| all)

功能：求协方差

Selectvariance (sal) from scott.emp;

2  日期函数

1.   ADD_MONTHS 

功能：增加或减去月份

举例：

Selecthiredate, add_months(hiredate,2) from scott.emp;

Selectto_char(add_months(to_date(‘199912‘, ‘yyyymm‘),2), ‘yyyymm‘) from dual;

举例：请查询最近三个月入职的员工（把hiredate增加3个月，如果新日期大于当前日期则满足查询要求）

Select *from emp where add_months(hiredate,3)>=sysdate

Select *from emp where hiredate>=add_months(sysdate,-3)

2.   LAST_DAY

功能：返回当月日期的最后一天

Selectlast_day(sysdate) from dual;

3.   MONTHS_BETWEEN(date1, date2) 

功能：给出date1-date2的月份

举例：

Selectmonths_between (to_date(‘2013.05.20‘, ‘yyyy.mm.dd‘), to_date(‘2014.04.20‘, ‘yyyy.mm.dd‘) ) mon_betw from dual;

4.   NEW_TIME(date, ‘this‘, ‘that‘)

功能：给出时间date在this’时区对应that’时区的日期和时间

举例：

Selectto_char(sysdate, ‘yyyy.mm.dd hh24:mi:ss‘)bj_time, to_char(new_time(sysdate, ‘PDT‘, ‘GMT‘), ‘yyyy.mm.dd hh24:mi:ss‘)los_angles from dual;

说明：this,that对应的时区及其简写，大西洋标准时间：AST或ADT；阿拉斯加_夏威夷时间：HST或HDT；英国夏令时：BST或BDT；美国山区时间：MST或MDT；美国中央时区：CST或CDT；新大陆标准时间：NST；美国东部时间：EST或EDT；太平洋标准时间：PST或PDT；格林威治标准时间：GMT；Yukou标准时间：YST或YDT。

5.   NEXT_DAY(date, ‘day‘)

功能：给出日期date和星期x以后计算下一个星期的日期

Selectnext_day(‘18-5月-2013‘, ‘星期五‘) next_dayfrom dual;  --下一个星期五是多少号？

6.   to_date (string, ‘format‘)   

功能：将字符串转换成oracle中的一个日期（format的格式）

注意：插入date列时默认以（日-月-年）格式。

yy：两位数字的年份  2004 ---> 04

yyyy：四位数字的年份   2004年

mm: 两位数字的月份  8月 --à08

dd: 2位数字的天   30号 -à30

hh24: 8点---à   20

hh12:   8点 ----à  08

mi、ss   ----à显示分钟\秒

举例：

insert into scott.emp(empno,hiredate)

values(2222,to_date(‘1988-11-11‘, ‘yyyy-mm-dd‘))

原文：http://blog.csdn.net/fangchao3652/article/details/26510533