SQL REPLACE 函數：在資料庫中搜尋並取代字串

摘要：在本教學中，您將學習如何使用 SQL REPLACE 函數在給定的字串中搜尋並取代所有出現的子字串。

SQL REPLACE 函數簡介

有時，您會想在欄位中搜尋並用新的子字串取代舊的子字串，例如，將失效的連結更改為新的連結，將過時的產品名稱重新命名為新的名稱等等。

SQL 提供了一個非常有用的字串函數，稱為 REPLACE，它允許您用新的子字串取代字串中所有出現的子字串。

以下說明 REPLACE 函數的語法

REPLACE(string, old_substring, new_substring);Code language: SQL (Structured Query Language) (sql)

REPLACE 函數將搜尋所有出現的 old_substring，並將其取代為 new_string。

以下語句將所有出現的 bar 取代為 foo，因此結果為 foo foo foo。

SELECT REPLACE('foo foo bar', 'foo', 'bar'); -- bar bar barCode language: SQL (Structured Query Language) (sql)

請注意，REPLACE 函數是以區分大小寫的方式搜尋子字串。例如，以下語句將 foo 取代為 bar，FOO 將不會被取代，因為它與搜尋的字串 foo 不匹配。

SELECT REPLACE('foo FOO bar', 'foo', 'bar'); -- bar FOO bar
Code language: SQL (Structured Query Language) (sql)

如果函數找不到子字串，則不會執行任何動作。例如，以下語句將返回原始字串，因為它找不到任何出現的子字串 BAR。

SELECT REPLACE('foo foo bar', 'BAR', 'bar'); -- foo foo barCode language: SQL (Structured Query Language) (sql)

使用 UPDATE 語句的 SQL REPLACE

讓我們看看範例資料庫中的 employees 表格。

以下語句返回員工姓名和他們的電話號碼。

SELECT 
    first_name, 
    last_name, 
    phone_number
FROM
    employees
ORDER BY first_name, last_name;Code language: SQL (Structured Query Language) (sql)

假設您想要使用破折號 (-) 字元來取代點 (.) 字元來格式化電話號碼。在這種情況下，您可以使用 UPDATE 語句，在 phone_number 欄位中將點字元取代為破折號字元，如下所示

UPDATE employees 
SET 
    phone_number = REPLACE(phone_number, '.', '-');Code language: SQL (Structured Query Language) (sql)

SQL REPLACE function replace dot with dash

請注意，上述 UPDATE 語句會更新 employees 表格中的所有列。

如果您在生產系統中更新資料，您應該先使用 SELECT 語句來找出受影響的列數，然後再進行大量更新。

例如，以下語句將員工的電子郵件從 sqltutorial.org 更新為員工 ID 為 100 的 acme.com

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com')
WHERE
    employee_id = 100;Code language: SQL (Structured Query Language) (sql)

讓我們檢查結果。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    email
FROM
    employees
WHERE
    employee_id = 100;    
Code language: SQL (Structured Query Language) (sql)

它如預期般工作。因此，我們可以透過移除 WHERE 子句將變更套用到所有列。

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com');Code language: SQL (Structured Query Language) (sql)

請注意，很容易犯錯，將欄位名稱作為 REPLACE 函數的第一個引數的字面字串使用，如下所示。

UPDATE employees 
SET 
    email = REPLACE('email',
        'sqltutorial.org',
        'acme.com')Code language: SQL (Structured Query Language) (sql)

您的意圖是用 acme.com 取代 email 欄位中的 sqltutorial.org。但是，此語句會將 email 欄位中的所有值更新為 email，因為以下表達式的結果是一個字面字串 email。

REPLACE('email','sqltutorial.org','acme.com'); -- emailCode language: SQL (Structured Query Language) (sql)

在本教學中，您學習了如何使用 SQL REPLACE 函數來搜尋並用新的字串取代所有出現的子字串。

這個教學對您有幫助嗎？