| 函數 | 說明 | 範例 |
|---|---|---|
| AVG() | 回傳數值集合平均值 | SELECT AVG(SALARY) FROM BSEMPMS; |
| CORR() / CORRELATION() | 回傳兩數值集相關係數 | SELECT CORRELATION(SALARY, BONUS) FROM BSEMPMS; |
| COUNT() | 回傳資料列或值的個數 | SELECT COUNT(*) FROM BSEMPMS; |
| COVAR() / COVARIANCE() | 回傳兩數值集協方差 | SELECT COVAR(SALARY, BONUS) FROM BSEMPMS; |
| MAX() | 回傳最大值 | SELECT MAX(SALARY) FROM BSEMPMS; |
| MIN() | 回傳最小值 | SELECT MIN(SALARY) FROM BSEMPMS; |
| STDDEV() | 回傳標準差 | SELECT STDDEV(SALARY) FROM BSEMPMS; |
| SUM() | 回傳總和 | SELECT SUM(SALARY) FROM BSEMPMS; |
| VAR() / VARIANCE() | 回傳變異數 | SELECT VARIANCE(SALARY) FROM BSEMPMS; |
| 函數 | 說明 | 範例 |
|---|---|---|
| ABS() / ABSVAL() | 絕對值 | SELECT ABS(-3.4) FROM BSEMPMS; |
| ACOS() | 反餘弦 | SELECT ACOS(0.9) FROM BSEMPMS; |
| ASIN() | 反正弦 | SELECT ASIN(0.9) FROM BSEMPMS; |
| ATAN() | 反正切 | SELECT ATAN(0.9) FROM BSEMPMS; |
| ATAN2(y, x) | 座標反正切 | SELECT ATAN2(0.5, 0.9) FROM BSEMPMS; |
| BIGINT() | 轉 64 位整數 | SELECT BIGINT(EMP_NO) FROM BSEMPMS; |
| CEILING() / CEIL() | 大於等於參數的最小整數 | SELECT CEILING(3.56) FROM BSEMPMS; |
| FLOOR() | 小於等於參數的最大整數 | SELECT FLOOR(88.93) FROM BSEMPMS; |
| ROUND(expr, n) | 四捨五入到小數點右第 n 位 | SELECT ROUND(2345.6789, 2) FROM BSEMPMS; |
| TRUNCATE() / TRUNC() | 截斷小數 | SELECT TRUNCATE(345.6789, 2) FROM BSEMPMS; |
| SQRT() | 平方根 | SELECT SQRT(36) FROM BSEMPMS; |
| POWER(x, y) | x 的 y 次方 | SELECT POWER(2, 5) FROM BSEMPMS; |
| MOD(x, y) | x 除以 y 的餘數 | SELECT MOD(20, 8) FROM BSEMPMS; |
| EXP() | 指數函數 e^x | SELECT EXP(2) FROM BSEMPMS; |
| LOG() / LN() | 自然對數 | SELECT LN(10) FROM BSEMPMS; |
| DOUBLE() | 轉雙精度浮點數 | SELECT DOUBLE('5678') FROM BSEMPMS; |
| FLOAT() | 轉浮點數 | SELECT FLOAT(789) FROM BSEMPMS; |
| REAL() | 轉單精度浮點數 | SELECT REAL(10) FROM BSEMPMS; |
| DECIMAL() | 轉定點數 | SELECT DECIMAL(SALARY, 12, 2) FROM BSEMPMS; |
| HEX() | 十六進位表示 | SELECT HEX(16) FROM BSEMPMS; |
| RAND() | 0~1 隨機浮點數 | SELECT RAND() FROM BSEMPMS; |
| 函數 | 說明 | 範例 |
|---|---|---|
| CHAR() | 日期/數值轉字串 | SELECT CHAR(SALARY, ',') FROM BSEMPMS; |
| CHR() | ASCII 碼轉字元 | SELECT CHR(167) FROM BSEMPMS; |
| CONCAT() / || | 字串串接 | SELECT CONCAT(EMP_NO, EMP_NAM) FROM BSEMPMS; |
| UCASE() / UPPER() | 轉大寫 | SELECT UPPER(EMP_NAM) FROM BSEMPMS; |
| LCASE() / LOWER() | 轉小寫 | SELECT LOWER(EMP_NAM) FROM BSEMPMS; |
| LTRIM() | 移除前導空白 | SELECT LTRIM(' CDDD') FROM BSEMPMS; |
| RTRIM() | 移除尾隨空白 | SELECT RTRIM('COMMENT ') FROM BSEMPMS; |
| TRIM() | 移除前後空白 | SELECT TRIM(' TEST ') FROM BSEMPMS; |
| SUBSTR(str, pos, len) | 擷取子字串 | SELECT SUBSTR('CDNJFDJFJD', 5, 2) FROM BSEMPMS; |
| POSSTR(str, substr) | 尋找子字串位置 | SELECT POSSTR('ABCDEFGH', 'D') FROM BSEMPMS; |
| REPLACE(str, old, new) | 替換字串 | SELECT REPLACE('ROMANDD', 'NDD', 'CCB') FROM BSEMPMS; |
| REPEAT(str, n) | 重複字串 n 次 | SELECT REPEAT('REPEAT', 3) FROM BSEMPMS; |
| SPACE(n) | 產生 n 個空白 | SELECT SPACE(10) FROM BSEMPMS; |
| ASCII() | 首字元 ASCII 碼 | SELECT ASCII('R') FROM BSEMPMS; |
| 函數 | 說明 | 範例 |
|---|---|---|
| YEAR() | 取年份 | SELECT YEAR('2003/01/02') FROM BSEMPMS; |
| MONTH() | 取月份 | SELECT MONTH('2003/10/20') FROM BSEMPMS; |
| DAY() / DAYOFMONTH() | 取日期 | SELECT DAY('2003/10/20') FROM BSEMPMS; |
| HOUR() | 取小時 | SELECT HOUR('18:34:23') FROM BSEMPMS; |
| MINUTE() | 取分鐘 | SELECT MINUTE('18:34:23') FROM BSEMPMS; |
| SECOND() | 取秒數 | SELECT SECOND('18:34:32') FROM BSEMPMS; |
| TIME() | 取時間部分 | SELECT TIME('2001-03-19.12.30.123456') FROM BSEMPMS; |
| DATE() | 取日期部分 | SELECT DATE('2001-03-19.12.30.123456') FROM BSEMPMS; |
| CURRENT DATE / TIME / TIMESTAMP | 系統當前日期/時間/時間戳 | SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1; |
| 函數 | 說明 | 範例 |
|---|---|---|
| COALESCE(v1, v2, ...) | 回傳第一個非 NULL 值 | SELECT COALESCE(BONUS, 0) FROM BSEMPMS; |
| VALUE(v1, v2) | 同 COALESCE（DB2 相容語法） | SELECT VALUE(BONUS, 0) FROM BSEMPMS; |
| NULLIF(v1, v2) | 若 v1=v2 則回傳 NULL | SELECT NULLIF(COMM, 0) FROM BSEMPMS; |
| CASE WHEN ... THEN ... ELSE ... END | 條件表達式 | SELECT CASE WHEN SALARY>50000 THEN 'HIGH' ELSE 'LOW' END FROM BSEMPMS; |
| NODENUMBER() | 回傳資料列分區號 | SELECT NODENUMBER(EMP_NO) FROM BSEMPMS; |
