Tracks
初级数据分析师 在 SQL 中
39小时
要点速览
-
REPLACE(string, old_substring, new_substring)一次调用即可替换所有匹配的子串 -
链式调用可替换多个子串：
REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd') -
传入空字符串即可移除字符：
REPLACE(phone, '-', '') -
若任一参数为
NULL，该行函数返回值为NULL -
大小写敏感性取决于数据库与排序规则（collation）——SQL Server 和 MySQL 默认不区分大小写；PostgreSQL 默认区分大小写
理解 SQL REPLACE() 函数
SQL 中的 REPLACE() 函数会在列中搜索指定的子串或字符串，并将其替换为另一个给定字符串。下面的示例展示了 REPLACE() 的基本用法。该函数的参数包括：
-
string： 需要进行替换的原始字符串。 -
old_substring： 需要被替换的子串。 -
new_substring： 用于替换旧子串的新子串。
REPLACE(string, old_substring, new_substring)当使用 REPLACE() 函数来修改表中的字符串时，查询语句语法如下，其中：
-
column_name： 用于搜索子串出现位置的列。 -
old_substring： 需要被替换的子串。 -
new_substring： 用于替换旧子串的新子串。
-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT
REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM
Products;REPLACE() 示例
SQL 的 REPLACE() 函数会将所有出现的字符串用指定的子串或字符串进行替换。在下面的示例中，SQL 的 REPLACE() 函数将 description 列中的值 old 替换为 new。查询会对每一行执行，将旧字符串替换为新字符串。
-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT
REPLACE(description, 'old', 'new') AS updated_description
FROM
Products;注意：MySQL、PostgreSQL、Oracle 和 SQL Server 数据库都支持 REPLACE() 函数。
REPLACE() 在 SQL 中的使用场景
在文本处理和数据转换中，SQL 的 REPLACE() 函数有许多重要用例，包括：
基础替换
您可以使用 SQL 的 REPLACE() 函数执行基础替换。假设我们的 SQL Server 数据库中有如下表：
Products 表示例。图片来源：作者。
使用以下 SQL 查询，您可以用 REPLACE() 函数将 old 状态更改为 new。
SELECT
product_id,
product_name,
price,
REPLACE(status, 'old', 'new') AS updated_status
FROM Products;得到的结果表如下所示。
使用 SQL REPLACE() 更新后的表。图片来源：作者。
替换多个子串
如果需要替换多个子串，可链式调用多个 REPLACE()。此处我们将 old 更新为 outdated，将 new 更新为 fresh。
-- Chain multiple REPLACE() functions to replace 'old' with 'outdated' and 'new' with 'fresh'
SELECT
product_id,
product_name,
price,
REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;结果表如下所示。
使用多个子串更新后的表。图片来源：作者。
移除字符
将空字符串作为第三个参数传入即可完全移除字符。这是从列中清除标点、多余空格或格式字符的最简洁方式。
-- Remove dashes from phone numbers
SELECT
customer_id,
REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;例如，'555-867-5309' 会变成 '5558675309'。同样的技巧也可用于移除导入数据中的特殊字符、编码杂质或不需要的空白。
处理大小写敏感
REPLACE() 的大小写敏感性取决于您的数据库与排序规则。PostgreSQL 默认区分大小写，因此 REPLACE(col, 'Old', 'New') 不会匹配 'old'。SQL Server 和 MySQL 遵循列或数据库的排序规则——大多数默认安装使用不区分大小写的排序规则，因此 REPLACE() 将不区分大小写进行匹配。
当您希望进行不区分大小写的替换时，应在替换前使用 UPPER() 或 LOWER() 将文本统一转换。此技巧有助于保持值的一致性。
-- Replace 'OLD' with 'OUTDATED', handling case sensitivity by converting to upper case
SELECT
product_id,
product_name,
price,
REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;
结合 REPLACE() 与 UPPER() 函数更新后的表。图片来源：作者。
-- Replace 'old' with 'outdated', handling case sensitivity by converting to lowercase
SELECT
product_id,
product_name,
price,
REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;
结合 SQL REPLACE() 与 LOWER() 函数更新后的表。图片来源：作者。
进阶技巧
这些技巧将 REPLACE() 与其他 SQL 函数结合，处理超出简单查找替换的场景。
使用动态值进行替换
可以通过与其他 SQL 函数组合来实现动态替换。例如，借助 REPLACE()，您可以使用另一列的值来替换某个子串。在下面的示例中，REPLACE() 将每个旧产品替换为 product_name 列中的实际值。
-- Replace 'old' with the value from product_name column
SELECT
product_id,
product_name,
price,
REPLACE(status, 'old', product_name) AS updated_status
FROM Products;
结合 REPLACE() 与动态值更新后的表。图片来源：作者。
在 UPDATE 语句中使用 REPLACE()
SQL 的 REPLACE() 函数可在 UPDATE 语句中使用，以修改一个或多行的列值。下面的查询将 status 列中的 old 更新为 outdated。
-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';若需处理更复杂的场景，例如通过连接另一张表来更新，请参阅我们的SQL UPDATE with JOIN教程。
将 REPLACE() 与其他字符串函数结合
一种进阶做法是在替换字符串值时，将 REPLACE() 与其他 SQL 字符串函数结合使用，例如 CHARINDEX() 或 SUBSTRING()。
-- Replace part of the product name starting from the first space with 'Updated'
SELECT
product_id,
product_name,
price,
-- Extract the substring of product_name starting from the first space to the end
REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;上述查询得到的结果表如下所示。
结合 REPLACE() 与其他字符串函数更新后的表。图片来源：作者。
将 NULL 替换为 0
您可以使用 COALESCE() 函数在调用 REPLACE() 之前将 NULL 值替换为后备值，如下所示。
-- Replace NULL with 'new' in the status column
SELECT
product_id,
product_name,
price,
REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;注意事项：若传给 REPLACE() 的任一参数为 NULL，该行的函数返回值即为 NULL——即使其他参数有效也如此。请在调用 REPLACE() 之前，用 COALESCE() 包装可能为 NULL 的列，而不是之后。
SQL REPLACE() 函数的实际应用
以下是 REPLACE() 在生产查询中大显身手的典型场景。
-
数据清洗：
REPLACE()可用于数据清洗，移除或替换数据集中字符串中的不需要字符或空格。 -
数据转换：
REPLACE()在数据转换中用于将值更改为不同格式，或将过时的值替换为新值。 -
处理用户输入：
REPLACE()有助于将用户输入的数据调整、转换为特定数据库可接受的统一标准，从而确保数据库的一致性。 -
文本标准化：
REPLACE()能标准化字符串列中的值以确保一致性，例如将缩写统一为小写或大写格式。 -
条件替换：
REPLACE()也可用于根据特定规则替换值，便于简化数据库管理实践。
各数据库对 REPLACE() 的实现
MySQL、PostgreSQL、Oracle 和 SQL Server 数据库均支持 SQL 的 REPLACE() 函数，且在这些数据库中的语法相近。
-- Replace 'old' with 'new' in the status column
SELECT
product_id,
product_name,
price,
REPLACE(status, 'old', 'new') AS updated_status
FROM Products;REPLACE() 的性能考量与最佳实践
使用 REPLACE() 函数时，请注意以下性能考量与最佳实践。
对查询性能的影响
REPLACE() 可能会影响查询性能，尤其在处理大型数据集时。其原因在于该函数会对每一行进行更新，导致查询执行时间较长。
索引考量
在将 REPLACE() 用于 WHERE 子句或 UPDATE 之前，请为查询目标列建立索引。没有索引时，数据库需要全表扫描来查找匹配项——在大表上代价尤其高昂。
监控与解决性能瓶颈
为解决查询性能相关问题，请考虑以下最佳实践：
-
使用数据库剖析工具监控查询性能，在处理大型数据集时进行优化。
-
当使用
REPLACE()修改字符串值时，定期维护索引以确保其保持有效。 -
采用批处理方式，在处理大型数据集时缩短查询执行时间。
若要学习更多用于数据转换的高级 SQL 函数，建议您学习 DataCamp 的 Learn SQL 课程。Reporting in SQL 课程也涵盖数据库优化相关主题，帮助您掌握不同的性能优化技巧。
结语
REPLACE() 函数几乎覆盖了各类 SQL 数据库中的文本替换需求。请牢记 NULL 行为——任一参数为 NULL 即返回 NULL——并在涉及大小写敏感时核查数据库的排序规则设置。
您可以通过学习 DataCamp 的 Intermediate SQL 与 SQL Fundamentals 课程进一步了解 SQL 中的 REPLACE() 函数。我也推荐 Associate Data Analyst in SQL 职业路径，深入学习 SQL 在数据转换中的应用并加速您的职业发展。最后，欢迎参加 DataCamp 的 SQL Associate Certification，展示您在数据分析中使用 SQL 的专业成就，在求职面试中脱颖而出。
如果您还想进一步了解数据分析中常用的多种 SQL 函数，建议阅读我其他教程：