Oracle 惯用函数的总结(一)
日期：2014-05-16 浏览次数：20413 次
文章转自：http://oracle.chinaitlab.com/induction/724839_5.html
?
?
SUBSTR(string, start , count):
取子字符串，从start开始（从1开始），去count个
SQL>select substr(12345678,2,5) from dual
substr(
---------
23456
?
?
?
REPLACE(string,s1,s2)
string 希望被替换的字符或变量
s1 被替换的字符串
s2
要替换的字符串
SQL> select replace(he love you,he,i) from dual;
REPLACE(H
----------
i love you
?
?
?
?
TRIM(s from string)
LEADING 剪掉前面的字符
TRAILING
剪掉后面的字符
如果不指定,默认为空格符
trim(' tech ') would return 'tech'
trim(' ' from ' tech ') would return 'tech'
trim(leading '0' from '000123') would return '123'
trim(trailing '1' from 'Tech1') would return 'Tech'
trim(both '1' from '123Tech111') would return '23Tech
?
?
?
?
?
ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
?
?
?
?
?
MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select
mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
?
?
?
?
FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
?
?
ROUND和TRUNC
按照指定的精度进行舍入
SQL> select
round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------
----------- ------------
56 -55 55 -55
?
elect round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
select round(123.456, 3) from dual; 回传 123.456
select round(-123.456, 2) from dual; 回传 -123.46
?
?
?
TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2)
trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
?
?
?
?
TO_CHAR(date,format)
SQL> select to_char(sysdate,yyyy/mm/dd
hh24:mi:ss) from dual;
TO_CHAR(SYSDATE,YY
-------------------
2004/05/09 21:14:41
?
?
?
TO_NUMBER
将给出的字符转换为数字
SQL> select to_number(1999) year from
dual;
YEAR
---------
1999
?
?
?
