Oracle SQL 語法

常用函數

來源：http://blog.spiderman.idv.tw/?p=413

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