數值函數
| 函數 | 說明 | 範例 |
| ABS(n) | 絕對值 | ABS(-5) = 5 |
| MOD(m,n) | 餘數 | MOD(10,3) = 1 |
| POWER(m,n) | m 的 n 次方 | POWER(2,3) = 8 |
| ROUND(m[,n]) | 四捨五入到 n 位小數 | ROUND(1.234,2) = 1.23 |
| TRUNC(m[,n]) | 截斷到 n 位小數 | TRUNC(1.234,2) = 1.23 |
字串函數
| 函數 | 說明 | 範例 |
| LOWER(str) | 全小寫 | LOWER('ABC') = 'abc' |
| UPPER(str) | 全大寫 | UPPER('abc') = 'ABC' |
| INITCAP(str) | 首字母大寫 | INITCAP('hello world') = 'Hello World' |
| CONCAT(s1,s2) | 串接 (等同 ||) | CONCAT('A','B') = 'AB' |
| LPAD(s,n[,pad]) | 左填充至長度 n | LPAD('5',3,'0') = '005' |
| RPAD(s,n[,pad]) | 右填充至長度 n | RPAD('5',3,'*') = '5**' |
| LTRIM(str[,set]) | 移除左側指定字元 | LTRIM(' abc') = 'abc' |
| RTRIM(str[,set]) | 移除右側指定字元 | RTRIM('abc. ','. ') = 'abc' |
| REPLACE(str,old[,new]) | 替換字串 | REPLACE('abc','a','X') = 'Xbc' |
| SUBSTR(str,pos[,len]) | 擷取子字串 | SUBSTR('abcdef',2,3) = 'bcd' |
| LENGTH(str) | 字串長度 | LENGTH('abc') = 3 |
| INSTR(s1,s2[,pos[,occ]]) | 搜尋子字串位置 | INSTR('abcabc','b',1,2) = 5 |
轉換函數
| 函數 | 說明 |
| NVL(expr1,expr2) | expr1 為 NULL 回傳 expr2 |
| TO_CHAR(n[,fmt]) | 數值/日期轉字串 |
| TO_NUMBER(str[,fmt]) | 字串轉數值 |
| TO_DATE(str[,fmt]) | 字串轉日期 |
TO_CHAR 數值格式常用符號
9:數字位,前導空白
0:強制顯示前導 0
$:美元符號
L:本地貨幣符號
.:小數點
,:千分位分隔
SELECT TO_CHAR(12345.67, '$99,999.99') FROM dual; -- $12,345.67
彙總函數
- AVG, COUNT, MAX, MIN, SUM
- STDDEV (標準差), VARIANCE (變異數)
- 支援 DISTINCT/ALL 關鍵字
日期函數
| 函數 | 說明 |
| SYSDATE | 目前系統日期時間 |
| ADD_MONTHS(d,n) | 日期加減 n 月 |
| LAST_DAY(d) | 該月最後一天 |
| MONTHS_BETWEEN(d1,d2) | 兩日期月份差 |
| NEXT_DAY(d,'星期') | 下一個指定週幾 |
| ROUND(d[,fmt]) | 日期四捨五入 |
| TRUNC(d[,fmt]) | 日期截斷 |
日期格式元素速查
- YYYY / YY:年份
- MM / MON / MONTH:月份
- DD / DDD:日
- HH24 / HH12 / MI / SS:時分秒
- DAY / DY:星期名稱
- FM:抑制前導零/空白
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 2024-05-20 14:30:00
實用範例精選
-- 移除地址尾部的點、st、rd
SELECT RTRIM(address, '. strd') FROM test;
-- 取子字串(負數從右算起)
SELECT SUBSTR('archibald bearisol', -9) FROM dual; -- bearisol
-- 找第 2 個空格位置
SELECT INSTR('li lei', ' ', 1, 2) FROM dual; -- 4