SQL REPLACE 函数

本文将解释 SQL REPLACE 函数，并探讨如何使用此函数通过新字符串更改或更新现有表列中的文本。让我们从解释其简单定义、语法和基本示例开始，包括替换表列、用 0 替换 NULL，以及用新字符串更新旧文本。

SQL REPLACE 函数可帮助将现有子字符串替换为用户指定的字符串。在处理数据库表时，我们可能会遇到拼写错误的单词或不准确的产品描述。此函数允许您操作这些数据。它查找错误的文本（无论是子字符串、单词还是字符），并用新数据替换该部分。

SQL Server Replace 函数在一些实际场景中发挥作用，例如：替换电话号码中的“()”，用零替换 NULL，以及更新拼写错误的城市名称。在所有这些情况下，手动逐一更改都是不切实际的。因此，最好使用 Replace 函数查找子字符串并将其替换为新字符串。

SQL Server Replace 函数语法

Replace 字符串函数语法如下：

SELECT REPLACE (Expression, string_pattern, Replacing_Text)
FROM [Source]

参数

  • Expression：要执行搜索的字符串。此函数将替换此 Expression 中存在的子字符串或单词。
  • string_pattern：要更改的文本，可以是字符、单词或子字符串。您在此处输入的任何内容，此 SQL 函数都会将其替换为新的。如果为空字符串作为参数值，则输出将返回未更改的文本。
  • Replacing_Text：您想嵌入 Expression 中的新句子。如果为空字符串作为 Replacing_Text，则输出将删除 string_pattern。

返回值

通常，它返回 VARCHAR 输出。但是，如果其中一个输入值为 NVARCHAR，则 REPLACE 函数将返回 NVARCHAR 输出。如果任何参数为 NULL，则输出为 NULL。

如果您需要更改列中的特定文本、更新值、替换字符串列中的数字以及替换 NULL 值，REPLACE 函数将是您的解决方案。我们使用以下数据来演示此 REPLACE() 函数。

Employee Source

SQL 字符串 Replace 函数示例

此字符串函数会用指定的文本更改原始文本。以下部分将展示使用字符串 REPLACE 函数的多种方法。

替换单个字符

如前所述，您可以使用此函数查找和替换单个字符。此查询将查找 tutorial Gateway 中所有出现的 t，并将其替换为 A。

SELECT REPLACE('tutorial Gateway', 't', 'A') result
AuAorial GaAeway

替换字符串中的单词或子字符串

在此示例中，SQL REPLACE() 函数在“Welcome to ABC Corporation. Enjoy ABC Workspace!”字符串中查找 ABC 单词。接下来，它将 ABC 的每次出现替换为 Amitabh Bachchan。

SELECT 
    REPLACE(
        'Welcome to ABC Corporation. Enjoy ABC Workspace!', 
        'ABC', 
        'Amitabh Bachchan'
    ) result;
Welcome to Amitabh Bachchan Corporation. Enjoy Amitabh Bachchan Workspace!

在此示例查询中，我们声明一个变量并分配数据。首先，我们将替换 @Expression 变量中的 Anything 为 Everything。接下来，我们直接使用它在“Welcome to Hello World”文本中将 Hello 更改为 New。

DECLARE @Expression varchar(50)
SET @Expression = 'Learn Anything From Here'

SELECT REPLACE (@Expression, 'Anything','Everything') AS 'Result' 

SELECT REPLACE ('Welcome to Hello World', 'Hello','New') AS 'Result'
Simple String literals Example

在表列上使用 REPLACE 函数 – 电子邮件

在以下两个示例中，我们使用带有额外列的 SELECT 语句来展示 REPLACE() 函数在表列上的工作功能。

它还允许您替换字符串的一部分、列值或变量旁边的文本。在此 字符串函数 示例中，我们将替换 SQL Server 中 Email 列中的 .com 为 .org。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,REPLACE ([Email], 'com','org' ) AS [Output]
 FROM [Employe]
SQL REPLACE Function 2

在此示例中，我们将替换 Email 列中的 @ 符号为空格。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,REPLACE([Email], '@','  ' ) AS [Result]
 FROM [Employe]
REPLACE Function on Email IDs

使用 SQL UPDATE 替换列中的字符串部分

上面显示的示例说明了此函数如何返回输出。但是，REPLACE 函数最常见的用法是在 UPDATE 语句中使用，以替换列中的特定字符串。例如，将特定类别的旧产品描述更改为新的、引人入胜的内容。

在此示例中，我们使用 REPLACE() 函数更新 Employe 表中的 Email 列。下面的查询会检查所有行以查找 yahoo.com。它将所有出现项替换为 tutorialgateway.org。这里，UPDATE 语句将实时更改列值，因此请务必小心。

UPDATE [Employe]
SET [Email] = REPLACE([Email],'yahoo.com','tutorialgateway.org');

SELECT * FROM [Employe];

更新替换 SQL

如果您将 REPLACE() 函数与 UPDATE 语句 和 WHERE 子句 结合使用，则可以将字符串替换限制为单个列值或满足条件的几行。

在此示例中，REPLACE() 函数将部门名称为 CEO 的员工的 Email 列从 hotmail.com 更新为 yahoo.com。这意味着我们正在替换单个值。

UPDATE [Employe]
SET [Email] = REPLACE([Email],'hotmail.com','yahoo.com')
WHERE DepartmentName = 'CEO';

SELECT * FROM [Employe];
UPDATE REPLACE 2

SQL 替换字符串部分

在上面的两个示例中，我们使用全新的文本更新了列中的整个字符串。但是，您可以使用 REPLACE 函数更新整个字符串的一部分。例如，您可以将所有员工的电子邮件域名从 abc.com 更改为 tutorialgateway.org，或替换域名扩展名等。下面的查询会将员工的电子邮件域名扩展名从 com 替换为 org。

UPDATE [Employe]
SET [Email] = REPLACE([Email],'com','org');

SELECT * FROM [Employe];

使用 REPLACE 函数删除单词

将 REPLACE() 函数与空字符串作为第二个参数一起使用，将从给定文本中删除单词或子字符串。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,REPLACE([DepartmentName], 'Software','' ) AS [Result]
 FROM [Employe]
Remove a Word with REPLACE Function

SQL 将 NULL 替换为 0

在实际情况中，NULL 值是最令人头疼的问题。在处理数据库时，您会遇到 NULL；您可以使用此函数来处理它们。例如，此函数将 NULL 替换为 0、默认值或默认文本。

下面的示例将 Employee 表 DeptID 列中的 NULL 替换为 0。这里，ISNULL 函数 查找 NULL 并将其更改为空字符串。接下来，REPLACE() 函数将空字符串更新为 0。您也可以在没有 REPLACE 函数的情况下使用 ISNULL([DeptID], ‘0’)。

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,[DeptID]
	  ,REPLACE(ISNULL([DeptID], ''), '', '0') AS [NewID]
  FROM [Employees]
Replace NULL with 0

SQL 替换正则表达式

到目前为止，我们解释了使用 REPLACE() 函数将现有文本更改为新文本。然而，真正的潜力在于使用正则表达式来查找要替换的字符串，它允许您使用正则表达式。

下面的查询会从客户电子邮件 ID 中删除数字。
PATINDEX() 函数 在 EmailAddress 列中查找从 0 到 9 的数字值或数字的位置。SUBSTRING 函数 从该位置读取前两个字符，因为每个电子邮件地址都有两个数字。然后 REPLACE() 函数将这两个数字替换为空字符串。如果电子邮件只有一个数字，请将查询从 2 更改为 1。

SELECT [CustomerKey],[FirstName],[LastName],[EmailAddress],
    REPLACE([EmailAddress],
        SUBSTRING([EmailAddress],
            PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN),
            2),'') AS Result
FROM [DimCustomer]
Replace regex Numbers in a String

评论已关闭。