REPLACE() 函數 (SQL REPLACE() Function)

REPLACE() 函數用於將字串中指定的子字串取代為新的字串。它會找出所有符合的子字串並進行替換。

REPLACE() 語法 (Syntax)

REPLACE(original_string, search_string, replacement_string)

• original_string：原始字串
• search_string：要搜尋並取代的子字串
• replacement_string：用來取代的新字串

REPLACE() 用法範例 (Example)

customers 資料表：

範例 1：基本字串取代

SELECT Name, REPLACE(Name, 'Smith', 'Johnson') AS NewName
FROM customers;

結果：

範例 2：更新 Email 域名

SELECT Email, REPLACE(Email, '@old.com', '@new.com') AS NewEmail
FROM customers;

結果：

範例 3：搭配 UPDATE 更新資料

UPDATE customers
SET Email = REPLACE(Email, '@old.com', '@new.com')
WHERE Email LIKE '%@old.com';

進階用法

1. 移除特定字元

將 replacement_string 設為空字串可以移除字元：

-- 移除所有空格
SELECT REPLACE('Hello World', ' ', '') AS NoSpaces;
-- 結果：HelloWorld

-- 移除電話號碼中的破折號
SELECT REPLACE(Phone, '-', '') AS CleanPhone
FROM customers;

2. 連續多次取代

如果需要取代多種字元，可以巢狀使用：

SELECT REPLACE(
    REPLACE(
        REPLACE(Phone, '-', ''),
        '(', ''
    ),
    ')', ''
) AS CleanPhone
FROM customers;

3. 區分大小寫

⚠️ 注意： REPLACE() 的大小寫敏感度取決於資料庫的排序規則 (Collation)。

MySQL (預設不區分大小寫)：

SELECT REPLACE('Hello HELLO hello', 'hello', 'hi');
-- 結果可能：hi hi hi (依 Collation 而定)

SQL Server (預設不區分大小寫)：

SELECT REPLACE('Hello HELLO hello', 'hello', 'hi');
-- 結果：hi hi hi

4. 處理 NULL 值

如果任一參數為 NULL，大多數資料庫會返回 NULL：

SELECT REPLACE(NULL, 'a', 'b');     -- 結果：NULL
SELECT REPLACE('abc', NULL, 'x');   -- 結果：NULL
SELECT REPLACE('abc', 'a', NULL);   -- 結果：NULL

實際應用範例

1. 清理資料格式

-- 標準化電話號碼格式
UPDATE contacts
SET Phone = REPLACE(REPLACE(REPLACE(Phone, ' ', ''), '-', ''), '+', '');

2. HTML 特殊字元編碼

SELECT REPLACE(
    REPLACE(
        REPLACE(Content, '&', '&'),
        '<', '&lt;'
    ),
    '>', '&gt;'
) AS EncodedContent
FROM articles;

3. URL 友善化

SELECT REPLACE(
    REPLACE(
        LOWER(Title),
        ' ', '-'
    ),
    '_', '-'
) AS UrlSlug
FROM posts;

4. 資料脫敏

-- 隱藏部分 Email
SELECT
    CONCAT(
        LEFT(Email, 2),
        REPLACE(
            SUBSTRING(Email, 3, INSTR(Email, '@') - 3),
            SUBSTRING(Email, 3, INSTR(Email, '@') - 3),
            '***'
        ),
        SUBSTRING(Email, INSTR(Email, '@'))
    ) AS MaskedEmail
FROM customers;
-- smith@example.com → sm***@example.com

5. 批量修正資料

-- 修正錯誤的城市名稱
UPDATE addresses
SET City = REPLACE(City, 'Taiepi', 'Taipei')
WHERE City LIKE '%Taiepi%';

與 TRANSLATE() 的差異 (Oracle / PostgreSQL)

Oracle 和 PostgreSQL 提供 TRANSLATE() 函數，可以一次取代多個單一字元：

-- REPLACE：取代整個子字串
SELECT REPLACE('abc', 'abc', 'xyz');  -- 結果：xyz

-- TRANSLATE：逐字元取代 (Oracle / PostgreSQL)
SELECT TRANSLATE('abc', 'abc', 'xyz');  -- 結果：xyz
SELECT TRANSLATE('abba', 'ab', 'xy');   -- 結果：xyyx

相關主題

• CONCAT() - 字串連接
• SUBSTRING() - 擷取子字串
• TRIM() - 去除空格
• LIKE - 模糊查詢