| 函數 | 說明 | 範例 |
|---|
| INSERT() | 在指定位置插入字串 | SELECT INSERT('abcdef', 3, 2, 'XY') FROM BSEMPMS -- 回傳 abXYef |
| LEFT() | 左側指定長度字串 | SELECT LEFT('ABCDEFG', 3) FROM BSEMPMS -- ABC |
| RIGHT() | 右側指定長度字串 | SELECT RIGHT('ABCDEFG', 3) FROM BSEMPMS -- EFG |
| TRANSLATE() | 字元轉換 | SELECT TRANSLATE('abc', 'ABC', 'abc') FROM BSEMPMS -- ABC |
| UPPER() / UCASE() | 轉大寫 (重複收錄) | SELECT UPPER('hello') FROM BSEMPMS |
| LOWER() / LCASE() | 轉小寫 | SELECT LOWER('HELLO') FROM BSEMPMS |
| LTRIM() / RTRIM() / TRIM() | 去除空格 | SELECT TRIM(' hello ') FROM BSEMPMS |
| 函數 | 說明 | 範例 |
|---|
| SIGN() | 正負號 (-1, 0, 1) | SELECT SIGN(-5), SIGN(0), SIGN(5) FROM BSEMPMS |
| ABS() / ABSVAL() | 絕對值 | SELECT ABS(-3.4) FROM BSEMPMS |
| CEILING() / CEIL() | 向上取整 | SELECT CEILING(3.56) FROM BSEMPMS |
| FLOOR() | 向下取整 | SELECT FLOOR(88.93) FROM BSEMPMS |
| ROUND() | 四捨五入 | SELECT ROUND(2345.6789, 2) FROM BSEMPMS |
| TRUNCATE() / TRUNC() | 截斷 | SELECT TRUNCATE(345.6789, 2) FROM BSEMPMS |
| MOD() | 餘數 | SELECT MOD(20, 8) FROM BSEMPMS |
| POWER() | 冪運算 | SELECT POWER(2, 5) FROM BSEMPMS |
| SQRT() | 平方根 | SELECT SQRT(36) FROM BSEMPMS |
| EXP() | 指數 | SELECT EXP(2) FROM BSEMPMS |
| LOG() / LN() | 自然對數 | SELECT LOG(10) FROM BSEMPMS |
| LOG10() | 底 10 對數 | SELECT LOG10(100) FROM BSEMPMS |
| RAND() | 隨機數 | SELECT RAND() FROM BSEMPMS |
| CASE WHEN ... THEN ... ELSE ... END | 條件運算式 | SELECT CASE WHEN SALARY > 50000 THEN 'HIGH' ELSE 'LOW' END FROM BSEMPMS |
| COALESCE() | 第一個非空 | SELECT COALESCE(COMM, 0) FROM BSEMPMS |
| NULLIF() | 相等則 NULL | SELECT NULLIF(VAL1, VAL2) FROM BSEMPMS |
| DECODE() (Oracle 相容) | 條件轉碼 | SELECT DECODE(DEPT, 'A', 'SALES', 'B', 'IT', 'OTHER') FROM BSEMPMS |
| 函數 | 說明 | 範例 |
|---|
| CURRENT DATE / CURRENT_DATE | 目前日期 | SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1 |
| CURRENT TIME / CURRENT_TIME | 目前時間 | SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1 |
| CURRENT TIMESTAMP / CURRENT_TIMESTAMP | 目前時間戳記 | SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 |
| USER / SESSION_USER | 目前使用者 | SELECT USER FROM SYSIBM.SYSDUMMY1 |
| IDENTITY_VAL_LOCAL() | 最近 IDENTITY 值 | INSERT ...; SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1 |
| GENERATE_UNIQUE() | 產生唯一值 | SELECT GENERATE_UNIQUE() FROM SYSIBM.SYSDUMMY1 |
| RAISE_ERROR() | 主動拋出錯誤 | SELECT RAISE_ERROR('78000', 'Custom error') FROM SYSIBM.SYSDUMMY1 |
