CASE WHEN(全數據庫通用)-- 簡單 CASE
CASE col WHEN val1 THEN res1 WHEN val2 THEN res2 ELSE res3 END
-- 搜索 CASE(最靈活)
CASE
WHEN col > 100 THEN 'High'
WHEN col > 50 THEN 'Medium'
ELSE 'Low'
END AS level
優點:標準、可移植、支持複雜邏輯、可嵌套。
缺點:語法稍冗長。
IIF / IF(方言函數)| 數據庫 | 函數 | 語法 | 備註 |
|---|---|---|---|
| SQL Server / Access / SQLite | IIF | IIF(condition, true_val, false_val) | 三參數,可嵌套 |
| MySQL / MariaDB | IF | IF(condition, true_val, false_val) | 三參數,注意:MySQL 還有 IF(expr1, expr2, expr3) 與 IFNULL 混淆 |
| Oracle | 無原生 IIF | 用 CASE 或 DECODE | DECODE(col, val1, res1, val2, res2, default) |
| PostgreSQL | 無 IIF | 用 CASE | 9.4+ 支持 NULLIF、COALESCE |
建議:跨平台代碼統一用 CASE WHEN,僅在確定單一平台且邏輯極簡時用 IIF/IF。
-- 統計各狀態訂單數(單掃描)
SELECT
SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) AS paid_cnt,
SUM(CASE WHEN status = 'REFUND' THEN 1 ELSE 0 END) AS refund_cnt,
SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS cancel_cnt,
COUNT(*) AS total
FROM orders;
| 功能 | SQL Server | MySQL / MariaDB | PostgreSQL | Oracle |
|---|---|---|---|---|
| 左起 N 字符 | LEFT(str, n) | LEFT(str, n) | LEFT(str, n) | SUBSTR(str, 1, n) |
| 右起 N 字符 | RIGHT(str, n) | RIGHT(str, n) | RIGHT(str, n) | SUBSTR(str, -n) |
| 任意位置截取 | SUBSTRING(str, start, len) | SUBSTRING(str, start, len) / SUBSTR | SUBSTRING(str FROM start FOR len) | SUBSTR(str, start, len) |
| 字符串長度(字符數) | LEN(str) / DATALENGTH(字節) | CHAR_LENGTH(str) / LENGTH(str)(字節) | LENGTH(str) / CHAR_LENGTH(str) | LENGTH(str)(字節) / LENGTHC |
| 查找子串位置 | CHARINDEX(sub, str, [start]) | LOCATE(sub, str, [start]) / INSTR(str, sub) | POSITION(sub IN str) / STRPOS(str, sub) | INSTR(str, sub, [pos], [occurrence]) |
| 替換 | REPLACE(str, old, new) | REPLACE(str, old, new) | REPLACE(str, old, new) | REPLACE(str, old, new) |
| 去除空白 | LTRIM/RTRIM/TRIM | TRIM([[BOTH|LEADING|TRAILING] remstr FROM] str) | TRIM/LTRIM/RTRIM | TRIM/LTRIM/RTRIM |
MID = SUBSTRING、LEFT/RIGHT 同標準SELECT
SUBSTRING('Hello World', 7, 5) AS sub1, -- World
MID('Hello World', 7, 5) AS mid1, -- World (別名)
LEFT('Hello World', 5) AS left1, -- Hello
RIGHT('Hello World', 5) AS right1; -- World
integer_expression 為負 → 返回空串(SQL Server);MySQL 返回全串。VARCHAR / NVARCHAR。-- 1. 截取第一個 @ 之前的用戶名
SELECT SUBSTRING_INDEX(email, '@', 1) AS username FROM users; -- MySQL 專用
-- 2. 統一手機號掩碼:138****1234
SELECT CONCAT(LEFT(mobile, 3), '****', RIGHT(mobile, 4)) AS masked FROM users;
-- 3. 解析 JSON 路徑(MySQL 5.7+ / SQL Server 2016+)
SELECT JSON_EXTRACT(data, '$.user.name') AS name; -- MySQL
SELECT JSON_VALUE(data, '$.user.name') AS name; -- SQL Server
LENGTH 返回字節數,CHAR_LENGTH 返回字符數,截取用字符數函數。CAST(num AS CHAR)。COALESCE/IFNULL 預處理。TEXT/BLOB 列需先 CAST(... AS VARCHAR(MAX)) 或用 SUBSTRING 直接操作(引擎依賴)。條件表達式用標準
CASE WHEN走天下;字符串函數記住「LEFT/RIGHT/SUBSTRING 三大金剛」,其餘查手冊。跨平台遷移時,建立函數映射表,單元測試覆蓋所有字符串邏輯。