COALESCE 排除空字串方法

2022-07-31 310 words 2 minutes

Contents

之前有寫過 mysql COALESCE 取代null值好方法 | 程式狂想筆記，這篇補充如何同時處理 NULL 和空字串的情況。

COALESCE 基本用法

COALESCE 會依序評估所有參數，回傳第一個非 NULL 的值：

1
2
3
4
5
6
7
8


-- 語法
COALESCE(value1, value2, ..., valueN)

-- 範例：若 phone 為 NULL，回傳 '無電話'
SELECT COALESCE(phone, '無電話') AS phone FROM users;

-- 多個備選值
SELECT COALESCE(mobile, phone, email, '無聯絡方式') AS contact FROM users;

NULLIF 的用法

NULLIF(a, b) 比較兩個值：若 a = b 則回傳 NULL，否則回傳 a：

1
2
3
4
5
6
7


-- 當 a 等於 b 時回傳 NULL
NULLIF(a, b)

-- 範例：若值為 0，視為 NULL
SELECT NULLIF(score, 0) AS adjusted_score FROM results;
-- score = 0 → NULL
-- score = 85 → 85

同時排除 NULL 和空字串

資料庫欄位有時會同時出現 NULL 和空字串 ''，若只用 COALESCE 只能處理 NULL，無法過濾空字串。組合 COALESCE + NULLIF 可同時處理兩種情況：

1
2
3


-- 若 Other 是 NULL 或空字串，則回傳 Industry
SELECT COALESCE(NULLIF(Other, ''), Industry) AS Ind
FROM registration;

執行邏輯：

NULLIF(Other, '') → 若 Other = ''，回傳 NULL；否則回傳原值
COALESCE(..., Industry) → 若第一個參數是 NULL，回傳 Industry

效果：

Other	Industry	結果
‘IT’	‘Tech’	‘IT’
''	‘Tech’	‘Tech’
NULL	‘Tech’	‘Tech’
NULL	NULL	NULL

多層 Fallback 範例

1
2
3
4
5
6
7
8


-- 依序：nickname → real_name → email 前綴 → 'Anonymous'
SELECT COALESCE(
    NULLIF(nickname, ''),
    NULLIF(real_name, ''),
    SUBSTRING_INDEX(email, '@', 1),
    'Anonymous'
) AS display_name
FROM users;

各資料庫的支援情況

資料庫	COALESCE	NULLIF	備注
MySQL 5.x / 8.x	✓	✓	完整支援
PostgreSQL	✓	✓	完整支援，另有 `IS DISTINCT FROM`
Oracle	✓	✓	另有 `NVL(val, default)` 可用
SQL Server	✓	✓	另有 `ISNULL(val, default)`
SQLite	✓	✓	完整支援

各資料庫的替代方案

Oracle（NVL 和 DECODE）：

1
2
3
4
5


-- NVL：相當於 COALESCE 的兩參數版本
SELECT NVL(phone, '無電話') FROM users;

-- NVL2：類似三元運算子
SELECT NVL2(phone, '有電話', '無電話') FROM users;

SQL Server（ISNULL）：

1
2
3
4
5


-- ISNULL：只接受兩個參數
SELECT ISNULL(phone, '無電話') FROM users;

-- 若要同時處理空字串：
SELECT ISNULL(NULLIF(phone, ''), '無電話') FROM users;

PostgreSQL（額外技巧）：

1
2


-- PostgreSQL 可用 NULLIF 搭配 TRIM 去除空白
SELECT COALESCE(NULLIF(TRIM(phone), ''), '無電話') FROM users;

參考資料

SQL Coalesce with empty string - Stack Overflow