SQL Server REPLACE() Function

The REPLACE() function can be used to manipulate data in SQL Server databases. The function allows users to search for a specific substring in a string and replace it with another substring. It is relevant for standardizing data, correcting formatting, updating outdated information, or removing unwanted characters from the database.

What is the REPLACE() function?

The REPLACE() function searches for all string occurrences in a column and substitutes them with the specified values. As a rule, the function is used with the UPDATE and SELECT statements and can be applicable to the following cases:

  • Replacing typos or incorrect values in multiple records
  • Removing extra spaces or specific symbols from a string
  • Updating database data in bulk
  • Managing content dynamically, for example, replacing placeholders in greeting or order messages
  • Converting date or number formats, such as a date from 'YYYY-MM-DD' to 'YYYY/MM/DD'
  • Replacing units of measurement or converting abbreviations to full text for more consistent analysis

General syntax

The basic syntax of the REPLACE() function is as follows: 

REPLACE(original_string, old_substring, new_substring)

Parameter descriptions

The parameters of the function mean the following:

  • string refers to the text where you want to make replacements, or it can be the column in which you search for the substring occurrences
  • old_substring refers to the substring you want to find and replace
  • new_substring refers to the new substring that will replace the old_substring

Simple example

Let us illustrate the use of the REPLACE() function. We'll create the EmployeeInfo table, including a JobTitle column, populate the table with data, and update some of those titles using the function. 

-- create a table 
CREATE TABLE EmployeeInfo (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
JobTitle VARCHAR(100)
);

-- insert data into the table
INSERT INTO EmployeeInfo (EmployeeID, EmployeeName, JobTitle)
VALUES
(1, 'John Doe', 'Sales Rep'),
(2, 'Jane Smith', 'Mgr of Sales'),
(3, 'Emily Johnson', 'Sales Mgr'),
(4, 'Michael Brown', 'Customer Support Rep'),
(5, 'Sarah Davis', 'Sales Mgr');

Now, we need to standardize the job titles by replacing 'Mgr' with 'Manager'. To do this, we'll execute the following UPDATE query, including the REPLACE() function in the SET clause: 

UPDATE EmployeeInfo
SET JobTitle = REPLACE(JobTitle, 'Mgr', 'Manager');

To check that the data has been updated, we'll run the SELECT query: 

 SELECT * FROM EmployeeInfo;

Use the REPLACE() function to update the values

Using REPLACE() in different contexts

You can use the REPLACE() function with a SELECT query to modify the output it returns without changing the actual data in the database. Let us illustrate some cases, such as data formatting, bulk updates, or dynamic content management.

Scenario 1: The Customers table stores phone numbers with dashes (-) separating the parts (for example, 123-456-7890), while we want to format the phone numbers to use spaces instead of dashes.

Before data formatting

To format phone numbers, execute the following SELECT query: 

SELECT CustomerID, 
    CustomerName, 
    REPLACE(PhoneNumber, '-', ' ') AS FormattedPhoneNumber
FROM Customers;

The result would be as follows:

After data formatting

Scenario 2: In the Products table, product codes that start with the prefix 'NEW' have become outdated and need to be updated throughout the table. Here's what the table looks like before the update.

Before bulk updates

To change the product codes from 'NEW' to 'OLD', execute the UPDATE query with the REPLACE() function: 

UPDATE Products
SET ProductCode = REPLACE(ProductCode, 'NEW', 'OLD');

Then, run the SELECT query to retrieve the data and check that it has been updated:

After bulk updates

Scenario 3: We manage the Messages table where placeholders, such as {UserName}, are used in message templates. We want to dynamically replace this placeholder with actual user names.

Before - dynamic content management

Next, run the SELECT query to replace 'username' with the actual customer name:

After - dynamic content management

These examples have illustrated how the REPLACE() function can be applied in different practical scenarios for manipulating data within a database.

Try it yourself with dbForge Studio

While the REPLACE() function is used for modifying data in a string, dbForge Studio for SQL Server is the ultimate IDE that offers advanced tools similar to this functionality, including bulk editing, find and replace, and SQL refactoring tools. These features provide a user-friendly way to manage and update data without writing SQL code.

  • Data Editor that allows you to view and edit data in the SQL tables. For example, you can filter data to display only rows with a specified value and then perform a bulk update
  • Find and Replace tool that allows you to search for specific text within your database objects. For example, you can open the Find and Replace dialog, specify the text to find and what to replace it with, and then apply the changes in your database objects, such as stored procedures, functions, views, and table data
  • Data Generator that can populate table columns with modified or formatted random data based on specific patterns or rules
  • SQL refactoring tools that allow you to rename or replace identifiers in your database schema, such as table names, column names, or variable names. While this is more about schema changes than data changes, it offers a way to automatically update all references in your SQL code

In addition, dbForge Studio boosts productivity with its intuitive interface and visual tools, including advanced code completion, SQL editing tools, and data and schema comparison capabilities. It also offers visual database design, integrated data generation and source control tools. Moreover, the SQL debugging and profiling tools of the Studio allow for efficient query optimization.

Watch this video to learn about the features Studio for SQL Server has and why you need to install it to simplify and improve your SQL development and management.

Emulating the REPLACE() function in SQL Server

However, there might be cases when the REPLACE() function cannot meet your requirements, and you have to use other techniques and methods to perform your tasks. For example, when you need to replace only the first occurrence of a substring instead of all occurrences, the REPLACE() function cannot be applied. Instead, you can utilize other functions for customized string replacements:

The STUFF() function inserts a string at a given position into another string. The syntax would be as follows: 

STUFF(string, start, length, new_string);

where:

  • string is the string you want to modify
  • start specifies the position within the string where you want to start the operation
  • length is the number of characters in the string to be removed starting from the start position
  • new_string is the string you want to insert into the original string at the specified start position

The function first removes a specified number of characters from the string, starting at the specified position, and then inserts the new string where the characters were removed.

The CHARINDEX() function finds where the first occurrence of a search string starts within a string. It is not case-sensitive. The function returns 0 if the substring is not found in the specified column. The syntax would be as follows: 

CHARINDEX(substring, string [, start_location]);

where:

  • substring is the substring you want to find in the string
  • string is the string in which you want to search for the substring
  • start_location specifies the position in the string where the search should begin

For example, we have the ComputingDevices table with the following data:

Using the STUFF() and CHARINDEX() functions

Then, we need to replace the first occurrence of 'SSD' with 'NVMe' for each product in the ProductDescription column. To do this, execute the following SELECT query: 

SELECT 
    ProductID,
    ProductName,
    STUFF(ProductDescription, 
          CHARINDEX('SSD', ProductDescription), 
          LEN('SSD'), 
          'NVMe') AS UpdatedDescription
FROM ComputingDevices cd;

Using the STUFF() and CHARINDEX() functions

As you can see, the products with the IDs 1 and 3 have been updated. However, the product with the ID 2 displays null in the UpdatedDescription column because the CHARINDEX('SSD', ProductDescription) function returns 0. This indicates that the substring 'SSD' was not found in the ProductDescription column for that row.

To sum up, the STUFF() and CHARINDEX() functions allow for more precise string manipulation. Still, they make your queries complex and difficult to read, and thus, may be less efficient for large datasets.

Case sensitivity

Let us discuss the case sensitivity of the REPLACE() function in SQL Server. By default, it is case-insensitive to the default collation settings. It means that the function does not distinguish between uppercase and lowercase characters. For example, it will consider 'World', 'world', and 'WORLD' as the same string.

However, if your database uses a case-sensitive collation, the REPLACE() function will also become case-sensitive. For example, if the collation on the required server is SQL_Latin1_General_CP1_CS_AS ('CS' means Case Sensitive), the REPLACE() function will only replace substrings that match the exact case of the search string.

Common pitfalls

It should be mentioned that if any of the arguments of the REPLACE() function is NULL, it will return 0. For example: 

SELECT REPLACE(NULL, 'a', 'b'); -- Returns NULL
SELECT REPLACE('Hello', NULL, 'b'); -- Returns NULL
SELECT REPLACE('Hello', 'a', NULL); -- Returns NULL

In addition, using this function in queries that need to scan and modify millions of rows can affect database performance. Moreover, applying the function to a column that is part of an index can make the index less efficient and slow down query performance. This is because the function must process the column values before any comparisons or index operations can occur.

Further learning

To effectively master dbForge Studio for SQL Server and leverage its capabilities for your database tasks, refer to the following learning resources:

Conclusion

We have explored the REPLACE() function, including its basic syntax, and different use cases. The function helps manipulate data in strings, remove unnecessary data, fix typos, update data in bulk, or format dates and numbers. We have also discussed scenarios when it is better to use the REPLACE() function, and situations in which the STUFF() and CHARINDEX() functions might be more appropriate alternatives. In addition, dbForge Studio offers the best features and tools for searching and updating output values with new ones.