oracle函数的用法

许多资料（包括Oracle 的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。

§2.2.1 单记录字符函数

函 数 ASCII CHR CONCAT INITCAT INSTR INSTRB LENGTH LENGTHB LOWER LPAD LTRIM RPAD RTRIM REPLACE SUBSTR SUBSTRB SOUNDEX TRANSLATE TRIM UPPER NVL 说 明 返回对应字符的十进制值 给出十进制返回字符 拼接两个字符串，与 || 相同 将字符串的第一个字母变为大写 找出某个字符串的位置 找出某个字符串的位置和字节数 以字符给出字符串的长度 以字节给出字符串的长度 将字符串转换成小写 使用指定的字符在字符的左边填充 在左边裁剪掉指定的字符 使用指定的字符在字符的右边填充 在右边裁剪掉指定的字符 执行字符串搜索和替换 取字符串的子串 取字符串的子串（以字节） 返回一个同音字符串 执行字符串搜索和替换 裁剪掉前面或后面的字符串 将字符串变为大写 以一个值来替换空值

ASCII()

是字符串。返回与指定的字符对应的十进制数。

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

A a ZERO SPACE ---------- ---------- ---------- ----------

65 97 48 32

SQL> select ascii('赵') zhao,length('赵') leng from dual;

ZHAO LENG

---------- ---------- 740 1

CHR([NCHAR])

给出整数，返回对应字符。如：

SQL> select chr(740) zhao,chr(65) chr65 from dual;

ZH C -- - 赵 A

CONCAT(,)

SQL> select concat('010-','8801 8159')||'转23' 赵元杰电话 from dual;

赵元杰电话

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

010-8801 8159转23

INITCAP()

返回字符串c1 并第一个字母变为大写。例如：

SQL> select initcap('simth') upp from dual; UPP ----- Simth

INSTR(,[,[,] ] )

在一个字符串中搜索指定的字符，返回发现指定的字符的位置。 C1: 被搜索的字符串 C2: 希望搜索的字符串

I: 搜索的开始位置，缺省是1 J: 出现的位置，缺省是1。

SQL> SELECT INSTR ('Oracle Training', 'ra', 1, 2) \"Instring\" FROM DUAL;

Instring

----------

9

INSTRB(,[,[,] ] )

除了返回的字节外 ，与INSTR 相同，

LENGTH( )

返回字符串 c 的长度。

SQL> l

1 select name,length(name),addr,length(addr),sal,length(to_char(sal)) 2* from nchar_tst SQL> /

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

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

赵元杰 3 北京市海淀区 6 99999.99 8

LENGTHB( )

以字节返回字符串的字节数。

SQL> select name,lengthb(name),length(name) from nchar_tst;

NAME LENGTHB(NAME) LENGTH(NAME)

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

赵元杰 6 3

LOWER ( )

返回字符串并将所有字符变为小写。

SQL> select lower('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

UPPER( )

与 LOWER 相反，将给出字符串变为大写。如：

SQL> select upper('AaBbCcDd') AaBbCcDd from dual;

AABBCCDD

--------

AABBCCDD

RPAD和LPAD(粘贴字符)

RPAD(string，Length[，'set']) LPAD(string，Length[，'set']) RPAD在列的右边粘贴字符； LPAD在列的左边粘贴字符。

例1：

SQL>select RPAD(City，35，'.')，temperature from weather;

RPAD(City，35，'.') temperature -------------------------- ---------------- CLEVELAND...... 85 LOS ANGELES.. 81 .........................

（即不够35个字符用'.'填满）

LTRIM(左截断)RTRIM（右截断） 函数

LTRIM (string [,‟set‟])

Left TRIM (左截断)删去左边出现的任何set 字符。

RTRIM (string [,‟set‟])

Right TRIM (右截断)删去右边出现的任何set 字符。

例1：

SELECT RTRIM („Mother Theresa, The‟, „The‟) “Example of Right Trimming” FROM DUAL;

Example of Right ---------------- Mother Theresa,

SUBSTR Substr(string，start[，Count])

取子字符串中函数 对字串（或字段），从start字符 开始，连续取 count 个字符并返回结果，如果没有指count则一直取到尾。

select phone,substr(phone,1,3) || ‘0’ || substr(phone,4) from telecommunication where master=’中国电信’;

SUBSTRB(string，start[，Count])

对字串（或字段），从start字节 开始，连续取 count 个字节并返回结果，如果没有指count则一直取到尾。

REPLACE (‘string’ [,’string_in’,’string_out’])

String: 希望被替换的字符串或变量。 String_in: 被替换字符串。 String_out: 要替换字符串。

SQL> select replace('Informaix中国公司','Informaix','IBM Informix') 2 IBM数据库 from dual;

IBM数据库 --------------------

IBM Informix中国公司

SOUNDEX( )

返回一个与给定的字符串读音相同的字符串（不管拼写是否一样）。

SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERE SOUNDEX(DPL_NAME) = SOUNDEX(„Saddam Hussain‟) ; DPL_NAME

---------------------------------------------- Al Husseni Sadda Al Sada.

REPLACE (‘string’ [,’string_in’,’string_out’])

String:希望被替换的字符串或变量。 String_in: 被替换字符串。 String_out: 要替换字符串。

SELECT REPLACE („Oracle‟, „Or‟, „Mir‟) “Example “ FROM DUAL; Example ------- Miracle

TRIM（ [] FROM ）

TRIM可以使你对给定的字符串进行裁剪（前面，后面或前后）。

 如果指定 LEADING, Oracle 从trim_char 中裁剪掉前面的字符；  如果指定TRAILING, Oracle 从trim_char 中裁剪掉尾面的字符；

 如果指定两个都指定或一个都没有给出，Oracle从trim_char 中裁剪掉前面及尾面的字

符；

 如果不指定 trim_character, 缺省为空格符；

 如果只指定trim_source, Oracle Oracle从trim_char 中裁剪掉前面及尾面的字符。 

例子：将下面字符串中的前面和后面的‘0‘字符都去掉：

SELECT TRIM (0 FROM 00098723400) \"TRIM Example\" FROM DUAL;

TRIM example

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

987234

select trim (' SECOOLER ') \"TRIM e.g.\" from dual; --最普通的用法

select trim (trailing from ' SECOOLER ') \"TRIM e.g.\" from dual; --

只取后面的空格

select trim (leading from ' SECOOLER ') \"TRIM e.g.\" from dual; --

只取前面的空格

select trim ('x' from 'xxxxSECOOLERxxxx') \"TRIM e.g.\" from dual; --指定的字符

select trim (trailing 'x' from 'xxxxSECOOLERxxxx') \"TRIM e.g.\" from dual;

select trim (leading 'x' from 'xxxxSECOOLERxxxx') \"TRIM e.g.\" from dual;

.需要注意的地方

这里的“trim_character”参数只允许包含一个字符，不支持多字符。

既然TRIM不能满足我们删除只剩“SECOOLER”字符串的要求，有么有其他手段呢？of course有。我们使用RTRIM和LTRIM“连环拳”完成这个任务。 1）使用RTRIM

sec@ora10g> select rtrim('xyxxSECOOLERxyyx','xy') \"e.g.\" from dual; e.g.

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

xyxxSECOOLER

2）使用LTRIM

sec@ora10g> select ltrim('xyxxSECOOLERxyyx','xy') \"e.g.\" from dual; e.g.

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

SECOOLERxyyx

3）联合使用RTRIM和LTRIM函数达到我们的目的

sec@ora10g> select ltrim(rtrim('xyxxSECOOLERxyyx','xy'),'xy') \"e.g.\" from dual; e.g. --------

SECOOLER

使用RTRIM和LTRIM函数时的注意事项：“xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作。

§2.2.2 单记录数字函数

函数 Value1 + value2 Value1 - value2 说明 加 减 Value1 * value2 Value1 / value2 ABS(value) CEIL(value) COS(value) COSH(value) EXP(value) FLOOR(value) LN(value) LOG(value) MOD(value,divisor) NVL(value,substitute) POWER(value,exponent) ROUND(value,precision) SIGN(value) SIN(value) SINH(value) SQRT(value) TAN(value) TANH(value) TRUNC(value,按precision) VSIZE(value) 乘 除 绝对值 大于或等于value的最小整数 余弦 反余弦 e的value次幂 小于或等于value的最大整数 value的自然对数 value的以10为底的对数 求模 value为空时以substitute代替 value的exponent次幂 按precision 精度4舍5入 value为正返回1;为负返回-1;为0返回 0. 余弦 反余弦 value 的平方根 正切 反正切 按照precision 截取value 返回value在ORACLE的存储空间大小

ABS( )

返回指定值的绝对值。如：

SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)

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

100 100

ACOS( )

给出反余弦的值。如：

SQL> Select acos(-1) acos from dual;

ACOS

---------- 3.14159265

ASIN( )

给出反正弦的值。如：

SQL> select asin(-1) \"arc sine\" from dual;

arc sine ---------- -1.5707963

ATAN （ ）

返回一个数字的反正切值。如：

SQL> select atan(-1) \"arc tangent\" from dual;

arc tangent ----------- -.78539816

CEIL( )

返回大于或等于给出数字的最小整数。如：

SQL> select ceil(3.14159) from dual;

CEIL(3.14159)

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

4

COS ( )

返回一个数字余弦值。如：

SQL> select cos(-3.1415926) from dual;

COS(-3.1415926)

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

-1

COSH ( )

返回一个数字双曲余弦值。如：

SQL> select cosh(20) cosh from dual;

COSH

----------

242582598

EXP ( )

返回一个数字 e 的 n 次方的值。如：

SQL> select exp(2),exp(1) from dual;

EXP(2) EXP(1) ---------- ---------- 7.30561 2.71828183

FLOOR ( )

对给定的数字取整数，如：

SQL> select floor(123.45),floor(45.56) from dual;

FLOOR(123.45) FLOOR(45.56)

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

123

45

LN( )

返回一个数字的对数值，n 是大于 0 的数字，如：

SQL> select ln(1),ln(2),ln(3) from dual;

LN(1) LN(2) LN(3)

---------- ---------- ---------- 0 .693147181 1.09861229

LOG( , )

返回一个以n1为底的n2的对数，n1不是0或1的正数。如：

SQL> select log(2,1),log(2,2) from dual;

LOG(2,1) LOG(2,2)

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

0 1

MOD( , )

SQL> Select mod(10,3), mod(10,2), mod(10,4) from dual;

MOD(10,3) MOD(10,2) MOD(10,4)

---------- ---------- ---------- 1 0 2

POWER ( , )

返回 n1 的 n2 次方值，如：

SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)

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

1024

27

ROUND(value,precision)

按照指定的精度进行舍入；

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

round(55.5) round(-55.5) trunc(55.5) trunc(-55.5) ----------- ------------ ----------- ------------- 56 -56 55 -55

SIGN()

取数字n 的符号，大于0 返回 1；小于0 返回-1； 等于0 返回0。

如： 例：

select sign(123), sign(-100),sign(0) from dual;

sign(123) sign(-100) sign(0) ---------- ---------- ---------- 1 -1 0

SIN ( )

返回一个数字的正弦值。如：

SQL> select sin(1.57079) from dual;

SIN(1.57079) ------------ 1

SINH( )

返回双曲余弦的值，如：

SQL> select sin(20),sinh(20) from dual;

SIN(20) SINH(20)

---------- ---------- .912945251 242582598

SQRT( )

返回 数字 n 的根，如：

SQL> select sqrt(),sqrt(10) from dual;

SQRT() SQRT(10)

---------- ---------- 8 3.16227766

TAN( )

返回数字 n的正切值，如：

SQL> select tan(20),tan(10) from dual;

TAN(20) TAN(10) ---------- ---------- 2.23716094 .8360827

TANH( )

返回数字 n的双曲正切值，如：

SQL> select tanh(20),tan(20) from dual;

TANH(20) TAN(20)

---------- ---------- 1 2.23716094

TRUNC(value,precision)

按照指定的截取一个数。如：

SQL> SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) from dual;

TRUNC1 TRUNC(124.16666,2) ---------- ------------------

100 124.16

§2.2.3 单记录日期函数

Oracle 用到的日期函是：

函 数 ADD_MONTH GREATEST(date1,date2,. . .) LAST_DAY( date ) LEAST( date1, date2, . . .) MONTHS_BETWEEN(date2,date1) NEXT_DAY( date,’day’) 描 述 在日期date上增加count个月 从日期列表中选出最晚的日期 返回日期date 所在月的最后一天 从日期列表中选出最早的日期 给出 Date2 - date1 的月数(可以是小数) 给出日期date之后下一天的日期，这里的day为星期，如： MONDAY,Tuesday等。 NEW_TIME(date,’this’,’other’) 给出在this 时区=Other时区的日期和时间 ROUND(date,’format’) 未指定format时，如果日期中的时间在中午之前，则将日期中的时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。 未指定format时，将日期截为12 A.M.( 午夜，一天的开始). TRUNC(date,’format’)

ADD_MONTHS( , )

增加月份和减去月份，如：

SQL> select to_char( add_months(to_date('199712','yyyymm'), 1),'yyyymm') add_month

2 from dual;

ADD_MO ------ 199801

SQL> select to_char(add_months(to_date('199712','yyyymm'), -1 ),'yyyymm') add_mo 2 from dual;

ADD_MO ------ 199711

LAST_DAY( date )

返回日期date 所在月的最后一天，如：

SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') 2 from dual;

TO_CHAR(SY TO_CHAR((S ---------- ---------- 2001.05.18 2001.05.19

MONTHS_BETWEEN(date2,date1)

给出 Date2 - date1 的月数(可以是小数)；

SQL> select months_between('19-12月-1999','19-3月-2000') mon_betw from dual;

MON_BETW ---------- -3

SQL> select months_between(to_date('2000.05.20','yyyy.mm.dd'), 2 to_date('2005.05.20','yyyy.mm.dd') ) mon_bet from dual;

MON_BET ----------

-60

NEW_TIME(date,’this’,’other’)

给出在this 时区=Other时区的日期和时间 This和other 是时区，它们可以是下面的值： 时区缩写 AST/ADT BST/BDT CST/CDT GMT HST/HDT MST/MDT NST PST/PDT YST/YDT 代表的时区 大西洋标准/日期时间 白令海标准/日期时间 中部标准/日期时间 格林威治时间 阿拉斯加-夏威夷标准/日期时间 山区标准/日期时间 新标准时间 太平洋标准/日期时间 Yukon标准/日期时间

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,

2 to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles 3 from dual;

BJ_TIME LOS_ANGLES

------------------- ------------------- 2001.05.19 06:25:25 2001.05.19 13:25:25

NEXT_DAY( date,’day’)

给出日期date和星期x之后计算下一星期x的日期，这里的day为星期，如： MONDAY,Tuesday

等。但在中文环境下，要写成’星期x’这样的格式，如：

例：比如今天是5月18日星期五，计算下一个星期五是几号：

SQL> select next_day('18-5月-2001','星期五') nxt_day from dual;

NXT_DAY ---------- 25-5月 -01

SYSDATE

用来得到系统的当前日期，如：

SQL> select to_char(sysdate,'dd-mon-yyyy day') from dual;

TO_CHAR(SYSDATE,'DD ------------------- 18-5月 -2001 星期五

TRUNC(,[,] )

按照 给出的 fmt 的要求将日期截断。如果 fmt=’mi’ 则表示截断掉秒保留至分。如：

SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,

2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH HHMM

------------------- ------------------- 2001.05.18 22:00:00 2001.05.18 22:27:00

§2.2.4 单记录转换函数

函 数 CHARTOROWID CONVERT HEXTORAW RAWTOHEX 描 述 将 字符转换到 rowid类型 转换一个字符节到另外一个字符节 转换十六进制到raw 类型 转换raw 到十六进制 ROWIDTOCHAR TO_CHAR TO_DATE TO_MULTIBYTE TO_NUMBER TO_SINGLE_BYTE 转换 ROWID到字符 转换日期格式到字符串 按照指定的格式将字符串转换到日期型 把单字节字符转换到多字节 将数字字串转换到数字 转换多字节到单字节

CHARTOROWID()

将字符数据类型转换为ROWID类型，如：

1* select rowid,rowidtochar(rowid),ename from scott.emp SQL> /

ROWID ROWIDTOCHAR(ROWID) ENAME ----------------------------------- ---------------------------------------- ---------- AAAFXDAABAAAHVaAAA AAAFXDAABAAAHVaAAA SMITH AAAFXDAABAAAHVaAAB AAAFXDAABAAAHVaAAB ALLEN AAAFXDAABAAAHVaAAC AAAFXDAABAAAHVaAAC WARD

CONVERT( ,[,] )

将源字符串sset从一个语言字符集转换到另一个目的dset字符集。

SELECT CONVERT („strutz‟, „ WE8HP‟, „ F7DEC „) “Conversion” FROM DUAL;

Conversion --------------- Strutz.

HEXTORAW( )

将一个十六进制构成的字符串转化为二进制。如：

Insert into printers( printer_nbr,manufacturer,model,init_string) Values ( 12,‟HP‟,‟Laserjet‟,”HEXTORAW(„1B45‟));

RAWTOHEX( )

将一个二进制构成的字符串转化为十六进制。如：

select rawtohex ( init_string) hext from printers where model=LaserJet‟ and manufacturer=‟HP‟; hext ----------- 1B45

ROWIDTOCHAR( )

将ROWID数据类型转换为字符类型，见 CHARTOROWID。

TO_CHAR(date,’format’)

根据format 重新格式日期date的格式。如：

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY ------------------- 2001/05/18 23:05:36

日期格式比较多，详细内容请参考原版资料。下面给出常用的日期格式代码： 日期格式代码表 日期代码 AD 或 BC A.D. 或B.C. AM或PM A.M.或P.M. DY DAY D DD DDD J W WW,IW MM MON 格式说明 AD=Anno Domini公元,BC=Before Christ公元前。不带点的公元或公元前 带点的公元或公元前 例子 ‘YYYY AD’=1999 AD ‘YYYY A.D.’=1999 A.D. AM= ante meridiem 上午，PM=post ‘HH12AM’=09AM meridiem下午。不带点的上午或下午 带点的上午或下午 星期几的缩写 星期几的全拼 一周的星期几，星期天=1，星期六=7 一月的第几天，131 一年的第几天，1366 公元前的第几天（从公元前4712起 ?） 一个月的第几周，1 5 一年的第几周，一年的ISO的第几周 两为数的月 月份的缩写 ‘HH12A.M.’=09A.M. Mon,Tue,... Monday,Tuesday,... 1,2,3,4,5,6,7 1，2，... 31 1,2,3,...366 2451514,2451515,... 1,2,3,4,5 1,2,3,4,... 52 01,02,03,...12 Jan,Feb,Mar ,...Dec MONTH RM YEAR SYYYY RR HH,HH12 HH24 MI SS SSSSS ../-;: ‘text’

月份的全拼 罗马数字的月份，I  XII 年的全拼 如果是公元前(BC),年份前负号 当前年份的后两位数字 12小时制，112 24小时制，023 一小时中的第几分，059 一分中的第几秒，059 一天中的第几秒，086399 标点符号表示法 引号表示法 January,February,... I,II,III,IV,...XII 1999,999,99,9 Nineteen Ninety-nine -1250 01代表2001年 1,2,3,...12 0,1,2,3,...23 0,1,2,3...59 0,1,2,3,...59 0,1,2,3,...86399 文字显示 文字显示 YYYY,YYY,YY,Y 四位数的年，三位数的年 TO_DATE(string,’format’)

将一和字串转换为ORACLE的日期。如：

Insert into demo(demo_key,date_col)

Values(1 , to_date(‟04-Oct-1999‟, „DD-Mon-yyyy‟) );

TO_MULTI_BYTE()

将字符串中的单字节字符转换为多字节字符, 如：

TO_NUMBER()

将给出的字符转换为数字，如：

SELECT TO_NUMBER („1947‟) “FISCAL_YEAR” FROM DUAL; FISCAL_YEAR ----------- 1947

TO_MULTI_BYTE()及TO_SINGLE_BYTE

将单字节转换为多字节或从多字节转换为单字节。

§2.2.5 其它的单记录函数

BFILENAME(