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'))
