SQL REPLACE Function

This article will explain the SQL REPLACE Function and explore how to use this function to change or update the existing table column’s text with a new string. Let’s start the page by explaining the simple definition, syntax, and basic example, replacing table columns, replacing Nulls with 0, and updating old text with the new string.

The SQL REPLACE function helps replace existing substring with a user-specified new string. While working with database tables, we encounter wrongly typed words or inaccurate product descriptions. This function allows you to manipulate that data. It finds the wrong text (whether a substring, word, or character) and replaces that part with new data.

Some real-time scenarios where the SQL Server Replace Function comes into play are: replacing the “( )” from the Phone Number, replacing NULLS with zeros, and updating misspelled city names. In all these scenarios, it is impractical to change one by one manually. So, it would be best to use the Replace Function to find the substring and replace it with a new string.

SQL Server Replace Function Syntax

The syntax of the Replace string function is as follows

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

Parameters

  • Expression: String to perform a search. This function will substitute a portion or word present in this Expression.
  • string_pattern: Text you want to change, and it can be a character, word, or substring. Anything you put here, this SQL Function will replace it with a new one. If you pass an empty string as the parameter value, the output returns unchanged text.
  • Replacing_Text: New sentence you want to embed into Expression. If you pass an empty string as the Replacing_Text, the output will remove the string_pattern.

Return Value

In general, it returns VARCHAR output. But if one of the input values is NVARCHAR, the REPLACE Function will return NVARCHAR output. If any argument is NULL, it returns NULL as an output.

The REPLACE function is your solution if you need to change the specific text within a column, update values, replace numbers from string columns, and replace NULL values. We use the data below for this REPLACE() function demonstration.

Employee Source

SQL String Replace Function Examples

This String Function changes the original text with the specified new one. The following section will show multiple ways to use the String REPLACE function.

Replace a Single Character

As we mentioned earlier, you can use this function to find and replace a single character. This query will find every occurrence of t in tutorial Gateway and replace it with A.

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

Replace a Word or Substring in a string

In this example, the SQL REPLACE() function looks for the ABC word within the ‘Welcome to ABC Corporation. Enjoy ABC Workspace!’ string. Next, it replaces each Occurrence of ABC with Amitabh Bachchan.

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

In this sample query, we declare a variable and assign the data. First, we will substitute the Anything with Everything from the variable @Expression. Next, we used it directly on the ‘Welcome to Hello World’ text to change Hello to 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

Using REPLACE Function on Table Columns – Email

In the below two examples, we use a SELECT statement with an extra column to showcase the working functionality of the REPLACE() Function on table columns.

It also allows you to replace the part of a string, column values, or text beside the variables. In this String Function example, we will substitute the .com with .org from the Email column in SQL Server.

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

In this example, we will substitute the @ symbol in the Email column with a space.

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

Use SQL UPDATE to Replace part of String in a Column

The above-shown example illustrates how this function returns the output. However, the most common use of the REPLACE function is using within an UPDATE statement to replace a specific string within a column. For example, changing a particular category’s old product description with new and engaging content.

In this example, we used the REPLACE() function to update the Email column from the Employe table. The below query checks all rows to find yahoo.com. It replaces every occurrence with tutorialgateway.org. Here, the UPDATE statement will change the column value in real time, so be careful.

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

SELECT * FROM [Employe];

Update Replace SQL

Suppose you use the REPLACE() Function with the combination of the UPDATE statement and WHERE clause. In that case, you can restrict the string replacement to a single column value or a few rows that satisfy the condition.

In this example, the REPLACE() function updates the Email column from hotmail.com to yahoo.com of an employee whose Department name is CEO. It means we are replacing a single value.

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

SELECT * FROM [Employe];
UPDATE REPLACE 2

SQL Replace Part of String

In the above two examples, we updated the whole string in a column with a completely new text. However, you can use the REPLACE function to update the part of a whole string. For example, you can change all the employees’ Email domain names from abc.com to tutorialgateway.org or replace domain extensions, etc. The below query replaces the employee’s email domain extensions from com to org.

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

SELECT * FROM [Employe];

Remove a Word with the REPLACE Function

Using the REPLACE() function with an empty string as the second argument will remove a word or substring from the given text.

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

SQL REPLACE NULL with 0

In real-time, NULL values are the biggest headache. While working with a database, you will encounter NULLS; you can use this function to deal with them. For instance, this Function replaces NULL with 0, default value, or default text.

The below example replaces the NULLS in the Employee table DeptID column with 0. Here, the ISNULL function finds and changes NULLs with an empty string. Next, the REPLACE() function updates the empty string with 0. You can also use ISNULL([DeptID], ‘0’) without the REPLACE function.

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

SQL Replace regex

Until now, we explained using the REPLACE() function to change existing text with new. However, the real potential is using regular expressions to find the string to replace, and it allows you to use regex.

The below query removes the numeric digits from the customer Email ID.
The PATINDEX() function finds the position of the numeric values or digits from 0 to 9 within the EmailAddress column. The SUBSTRING function reads the first two characters from that position because every email address has two digits. Then the REPLACE() function replaces those two digits with the empty string. If the email has a single digit, change the query from 2 to 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
Categories SQL

Comments are closed.