1.常用函數
1.1 字元函數
=============================================================
1.1.1 ASCII
ASCII(char)
select ASCII(’A'), ASCII(’ABC’) from dual
=============================================================
1.1.2 CHR
CHR(n [USING NCHAR_CS])
select CHR(68), CHR(68 USING NCHAR_CS) from dual
=============================================================
1.1.3 CONCAT
CONCAT(string1, string2)
兩個字串的連結, 等同於使用連接運算子( || ).
select CONCAT(’Good’,’ Morning’), ‘Good’ || ‘ Morning’ from dual
=============================================================
1.1.4 INITCAP
INITCAP(string)
將一個字串中每個單字的第一個字母, 改變為大寫, 而將其它字母變成小寫.
select INITCAP(’good MORNING’) from dual
=============================================================
1.1.5 INSTR
INSTR(string1, string2,[, n[ ,m]])
搜尋string1, 以便找到string2, 並回傳在string1 中該字元的位置,
亦即string2 在 string1 中的開始之處.
select INSTR(’easy com , easy go’, ‘easy’) from dual
select INSTR(’easy com , easy go’, ‘easy’,1,2) from dual
=============================================================
1.1.6 LENGTH
LENGTH(string)
回傳字串中的字元數量
select length(’ABCD’) from dual
select length(’ABCD長度’) from dual
=============================================================
1.1.7 LOWER, UPPER
LOWER(string)
UPPER(string)
將字串中的所有字元轉換為大(小)寫.
select lower(’AbCd’) from dual
select upper(’AbCd’) from dual
=============================================================
1.1.8 RPAD, LPAD
RPAD(string1, n[string2])
LPAD(string1, n[string2])
填補字元(空白)至第N個位置
select rpad(’ABCD’,30,’A') from dual
select lpad(’ABCD’,30,’A') from dual
select rpad(’ABCD’,30) from dual
=============================================================
1.1.9 RTRIM, LTRIM, TRIM
LTRIM(string1,[,string2])
RTRIM(string1,[,string2])
TRIM(string1,[,string2])
移除所指定的字元, (預設值空白)
select rtrim(’ ABCD ‘) from dual
select ltrim(’ ABCD ‘) from dual
select trim(’ ABCD ‘) from dual
select trim(’ ABCD ‘) from dual
SELECT LTRIM(’JJJJJeryyyyyyJJ’,'J’) FROM DUAL
=============================================================
1.1.10 REPLACE
REPLACE(string, search_string, [,replacement_string])
字串取代
select replace(’I LOVE YOU’,'LOVE’,'HATE’) from dual
select replace(’I LOVE YOU’,'LOVE’) from dual
=============================================================
1.1.11 SUBSTR
SUBSTR(string, m [, n] )
回傳一個字串的一部份, m若為負值, 用法如同right function
select substr(’abcdefghijk’,3,2) from dual
select substr(’abcdefghijk’,-3,2) from dual
select substr(’abcdefghijk’,3) from dual
=============================================================
1.1.12 TRANSLATE
TRANSLATE(string, from_string, to_string)
將一個字元組轉換成另一個字元組, 來修改字串.
select translate(’smmfrr space’,'drmfslc’,'1234567′) from dual
=============================================================
1.2 轉換函式
=============================================================
1.2.1 TO_CHAR
TO_CHAR(d [, fmt [, ‘nlsparams’] ] )
將一個日期/時間的值, 轉換為一個以字元為基礎的值.
TO_CHAR(n [, fmt [, ‘nlsparams’] ] )
將一個數值轉換為一個以字元為基礎的值.
數字格式元??
9 用來控制要被顯示之數字的有效位數
0 前導0
$ 會以一個前置的錢字號來顯示
, 在輸出中放置一個逗號
. 標記小數點
B 強迫0值被顯示為空白
S 用於一個格式字串的開始或結束處, 來顯示(+/-)值
select to_char(’1234′,’9999′) from dual => 1234
select to_char(’1234′,’99999′) from dual => 1234
select to_char(’1234′,’999′) from dual => ####
select to_char(’1234′,’0000′) from dual => 1234
select to_char(’1234′,’000000′) from dual => 001234
select to_char(’1234′,’000′) from dual => ####
select to_char(’1234′,’S0999999′) from dual
select to_char(’1234′,’0999990S’) from dual
select to_char(’1234′,’$99999′) from dual
select to_char(’00120340′,’B999999′) from dual
select to_char(’1234′,’$99,999′) from dual
select to_char(’1234′,’S09999.99′) from dual
=============================================================
1.2.2 TO_DATE
TO_DATE(string [, fmt [, ‘nlsparams’]] )
將一個日期/時間值的字元字串, 轉換為date型別的值.
日期格式元??
格式元素 函式
DAY 日的名稱(Saturday, Sunday, Monday等)
DD 月份的天
DDD 年的天
DY 天的縮寫名稱(Sat, Sun, Mon等)
HH 一天的小時
HH12 一天的小時, 同HH
HH24 一天的小時, 24小時制
MI 分鐘
MM 月份數字
MON 三個字母的月份縮寫
MONTH 完整拼出的月份名稱
Q 一年中的季
SS 秒
WW 年的週
YYYY 四位數的年
YYY 年份的最後三位數
YY 年份的最後二位數
Y 年份的最後一位數
select to_date(’2004/03/10′,’YYYY/MM/DD’) from dual
select to_date(’2004/10/03′,’YYYY/DD/MM’) from dual
select to_date(’20040310′,’YYYYMMDD’) from dual
=============================================================
1.2.3 TO_NUMBER
TO_NUMBER(string [, fmt [, ‘nlsparams’] ] )
將字元型態轉換為數值型態
select * from user_tables order by to_number(INITIAL_EXTENT)
=============================================================
1.2.4 NVL
NVL(expr1, expr2)
如果一個給定的輸入值為null時, 會回傳一個另一值, 以便使用。假如expr1是null時, nvl會回傳expr2; 否則, 它會單純地回傳expr1.
select username, nvl(to_char(lock_date),’Not Locked’) from dba_users
=============================================================
1.2.5 DECODE
DECODE (expr , search , result [ , search , result…..] [ , default ] )
一個類似IF敘述的能力。
一般用法:
SELECT name, DECODE(
plugged_in,
0, ‘Not Plugged In’,
1,’Plugged In’,
‘Invalid plugged_in value’
) plugged_in
FROM v$datafile
進階用法:
SELECT SUM(DECODE(owner,’SYS’,1,0)), SUM(DECODE(owner,’SYSTEM’,1,0))
FROM dba_objects
巢式用法:
select owner, table_name, column_name,
DECODE(data_type,
‘VARCHAR2′,’VARCHAR2 (’ || TO_CHAR(DATA_LENGTH) || ‘)’,
‘NUMBER’, decode(data_precision,
NULL, ‘NUMBER’,
‘NUMBER (’ ||
TO_CHAR(DATA_PRECISION) || ‘,’ ||
TO_CHAR(data_scale || ‘)’ )))
from dba_tab_columns
where data_type in (’VARCHAR2′,’NUMBER’)
=============================================================
1.3 數字函式=============================================================
1.3.1 ABC
ABC(n)
回傳一個數字的絕對值
select ABS(-1), ABS(1) from dual
=============================================================
1.3.2 MOD
MOD (m,n)
回傳m除以n的餘數
select MOD(18,12), MOD(30,12), MOD(30,30) from dual
=============================================================
1.3.3 SIGN
SIGN(n)
回傳一個值, 以指出n的符號.
(-1 負數, 0 數字為零, 1 正數)
select SIGN(76), SIGN(0), SIGN(-76.17) from dual
=============================================================
1.3.4 GREATEST, LEAST
GREATEST (expr [ , expr…..] )
LEAST (expr [ , expr…..] )
從所提供之引數的列表中, 回傳最大(小) 值.
select GREATEST(1,2,3) from dual
select GREATEST(’One’,'Two’) from dual
select LEAST(1,2,3) from dual
select GREATEST(TO_DATE(’05/18/2004′,’MM/DD/YYYY’),
TO_DATE(’04/01/2004′,’MM/DD/YYYY’)) from dual
1.3.5 ROUND
ROUND(n, m)
把一個值進位到所指定之特定小數點的位數.
參數:
n: 指定一個將進位的值
m:
select ROUND(123.45), ROUND(123.45,1), ROUND(123.45,-1) from dual
字串常用函數
1. 字串的開始位置是1
2. 函數中最後一個字是'B'的，表示該函數，有能力處理單位元資料與處理雙位元資料
A.字串函數傳回字串值
CHR(n)
--可將ASCII CODE 轉成database character set
ex.
select CHR(67)||CHR(65) AA from dual;
AA
----------
CA
select ASCII('屋') from dual;
ASCII('屋')
----------
43982
select CHR(43982) from dual;
CHR(43982)
----------
CONCAT(char1,char2)
--◎ 可連結char1及char2兩個字串
--◎ char1、char2可以是資料欄名稱
--◎ 與「||」之字串連結符號作用相同 concat('AA','BB') 可改成 'AA'||'BB'
ex.
select concat('AA','BB') AA from dual;
AA
----------
AABB
INITCAP(chars)
--◎ 將每一個字的字首轉成大寫
--◎ 只影響英文，不影響中文
ex.
select INITCAP('aa') AA from dual;
AA
----------
Aa
LOWER(chars)
--◎ 將大寫的字串轉成小寫
--◎ 只影響英文，不影響中文
ex.
select LOWER('AAAA') AA from dual;
AA
----------
aaaa
UPPER(chars)
--◎ 將小寫的字串轉成大寫
--◎ 只影響英文，不影響中文
ex.
select UPPER('aaaa') AA from dual;
AA
----------
AAAA
LPAD(char1,n[,char2])
--◎ 將字串char1往右靠，不足n個字元則補字串char2
--◎ char2預設值為一個空白
ex.
select LPAD('aa',5,'*') AA from dual;
AA
----------
***aa
RPAD(char1,n[,char2])
--◎ 將字串char1往左靠，不足n個字元則補字串char2
--◎ char2預設值為一個空白
ex.
select RPAD('aa',5,'*') AA from dual;
AA
----------
aa***
LTRIM(char1[,set])
--◎ 將字串char1最左邊開始去除set包含的字元
--◎ set預設值為一個空白
--◎ 對數字無效
ex.
select LTRIM('aaBBBBaa','a') AA from dual;
AA
----------
BBBBaa
RTRIM(char1[,set])
--◎ 將字串char1最右邊開始去除set包含的字元
--◎ set預設值為一個空白
--◎ 對數字無效
ex.
select RTRIM('aaBBBBaa','a') AA from dual;
AA
----------
aaBBBB
REPLACE(char,search_string[,replacement_string])
--◎ 在char字串中找尋search_string，找到後將其轉換成replacement_string
--◎ 若省略replacement_string，則將removed search_string
ex.
select REPLACE('aaBBBBaa','a','c') AA from dual;
AA
----------
ccBBBBcc
select REPLACE('aaBBBBaa','a') AA from dual;
AA
----------
BBBB
TRANSLATE(char,from,to)
--◎ 將字串char由from改為to
ex.
select TRANSLATE(12345.12,'12345','ABCDE') AA from dual;
AA
----------
ABCDE.AB
select TRANSLATE('abaaa','abcde','ABCDE') AA from dual;
AA
----------
ABAAA
SUBSTR(char,m[,n])
--◎ 將字串char由第m個字元開始擷取n個字元
--◎ 若m設成0，仍由第一個字元開始擷取；若m設成負數，則會由字串的最後開始找起
--◎ 若省略n，則會傳回m後的所有字元；若n為負數，則傳回null值
ex.
select SUBSTR('abcdef',-3,2) AA from dual;
AA
----------
de
select SUBSTR('abcdef',3,-2) AA from dual;
AA
----------
-- 傳回null
SUBSTRB(char,m[,n])
-- 用法和substr相同，但substrb可處理multi-byte的資料
-- 若資料庫只能處理single-byte的資料，則substr和substrb執行的結果會相同
ex.
select SUBSTRB('測試',3,2) AA from dual;
AA
----------
試
B.字串函數傳回數字值
INSTR(char1,char2[,n[,m]])
--◎ 在char1第n個字元開始尋找，第m次遇到字串char2的位置
--◎ n、m預設值為1，可省略
--◎ 若要尋找中文可使用INSTRB
ex.
select INSTR('aaBBBBaa','B') AA from dual;
AA
----------
3
select INSTR('台北 aaa','北') AA from dual;
AA
----------
2
INSTRB(char1,char2[,n[,m]])
--◎ 在char1第n個字元開始尋找，第m次遇到字串char2的位置
--◎ n、m預設值為1，可省略
--◎ 用法和INSTR相同，可處理multi-byte的資料
ex.
select INSTRB('中秋秋月圓','秋',1,2) AA from dual;
AA
----------
5
LENGTH(char)
--◎ 可計算出字串char的長度
LENGTHB(char)
--◎ 可計算出字串char的長度
--◎ 欄位宣告為char的資料型態，且欄位資料包含中文時使用
參考來源
http://blog.spiderman.idv.tw/?p=413
http://blog.blueshop.com.tw/pili9141/category/2156.aspx
