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，因此结果为 bar bar bar。

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)

SQL REPLACE 与 UPDATE 语句

让我们看看示例数据库中的 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 语句查找受影响的行数，然后再执行批量更新。

例如，以下语句将 employee id 为 100 的员工的电子邮件地址从 sqltutorial.org 更新为 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)

您的目的是将 email 列中的 sqltutorial.org 替换为 acme.com。然而，此语句会将 email 中的所有值更新为 email，因为以下表达式的结果是文字字符串 email。

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

在本教程中，您学习了如何使用 SQL REPLACE 函数搜索并替换子字符串的所有出现内容，替换内容为新的字符串。

本教程是否有用？