| 函數 | 說明 | 範例 |
|---|
| 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() | 座標反正切 | 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 |
| POWER() | 冪運算 | SELECT POWER(2, 5) FROM BSEMPMS |
| SQRT() | 平方根 | SELECT SQRT(36) FROM BSEMPMS |
| RAND() | 0-1 隨機浮點數 | SELECT RAND() FROM BSEMPMS |
| EXP() | 指數函數 | SELECT EXP(2) FROM BSEMPMS |
| LOG() / LN() | 自然對數 | SELECT LOG(10) FROM BSEMPMS |
| MOD() | 餘數 | SELECT MOD(20, 8) FROM BSEMPMS |
| 函數 | 說明 | 範例 |
|---|
| ASCII() | 最左字元 ASCII 碼 | SELECT ASCII('R') FROM BSEMPMS |
| CHR() | ASCII 碼轉字元 | SELECT CHR(167) FROM BSEMPMS |
| CONCAT() | 字串連接 | SELECT CONCAT(EMP_NO, EMP_NAM) FROM BSEMPMS |
| CHAR() | 數值/日期轉字串 | SELECT CHAR(SALARY, ',') FROM BSEMPMS |
| VARCHAR() | 轉可變長字串 | SELECT VARCHAR(EMP_NAM, 50) FROM BSEMPMS |
| UCASE() / UPPER() | 轉大寫 | SELECT UCASE(EMP_NAM) FROM BSEMPMS |
| LCASE() / LOWER() | 轉小寫 | SELECT LCASE(EMP_NAM) FROM BSEMPMS |
| SUBSTR() | 子串 | SELECT SUBSTR('CDNJFDJFJD', 5, 2) FROM BSEMPMS |
| LOCATE() / POSSTR() | 搜尋子串位置 | SELECT LOCATE('D', 'ABCDEFGH') FROM BSEMPMS |
| LTRIM() | 去除前導空格 | SELECT LTRIM(' CDDD') FROM BSEMPMS |
| RTRIM() | 去除尾部空格 | SELECT RTRIM('COMMENT ') FROM BSEMPMS |
| REPLACE() | 字串替換 | SELECT REPLACE('ROMANDD', 'NDD', 'CCB') FROM BSEMPMS |
| REPEAT() | 重複字串 | SELECT REPEAT('REPEAT', 3) FROM BSEMPMS |
| SPACE() | 產生空格字串 | SELECT SPACE(10) FROM BSEMPMS |
| HEX() | 十六進位表示 | SELECT HEX(16) 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 |
| DAYS() | 自 0001-01-01 起算天數 | SELECT DAYS(DATE('2001-06-05')) FROM BSEMPMS |
| 函數 | 說明 | 範例 |
|---|
| CAST() | 型別轉換 | SELECT CAST(CURRENT TIME AS CHAR(8)) FROM BSEMPMS |
| COALESCE() | 第一個非空值 | SELECT COALESCE(MGRNO, 'ABSENT') FROM DEPARTMENT |
| NULLIF() | 相等回傳 NULL | SELECT NULLIF(EXP1, EXP2) FROM BSEMPMS |
| ROUND() | 四捨五入 | SELECT ROUND(2345.6789, 2) FROM BSEMPMS |
| TRUNCATE() / TRUNC() | 截斷 | SELECT TRUNCATE(345.6789, 2) FROM BSEMPMS |
| DOUBLE() | 雙精度浮點數 | SELECT DOUBLE('5678') FROM BSEMPMS |
| REAL() | 單精度浮點數 | SELECT REAL(10) FROM BSEMPMS |
| FLOAT() | 浮點數表示 | SELECT FLOAT(789) FROM BSEMPMS |
| DECIMAL() | 定點數 | SELECT DECIMAL(123.456, 10, 2) FROM BSEMPMS |
| INTEGER() / INT() | 整數 | SELECT INTEGER(3.14) FROM BSEMPMS |
| SMALLINT() | 小整數 | SELECT SMALLINT(100) FROM BSEMPMS |
| NODENUMBER() | 分區號 | SELECT NODENUMBER(EMP_NO) FROM BSEMPMS |
