Skip to main content
HomeTutorialsSQL

How to Use the SQL REPLACE() Function

Learn the application of the SQL REPLACE() function in text manipulation. Understand the use of the REPLACE() function in data cleaning and database management.
Jul 2024  · 10 min read

The SQL REPLACE() function is useful for text manipulation. The REPLACE() function changes a part of a string with another specified substring. In most cases, the SQL REPLACE() function is used to modify all the substring occurrences within a string in a given dataset.

Text manipulation is important in SQL as it helps clean data for efficient analysis. The technique also helps in data transformation and optimization for improved performance.

If you are an aspiring data analyst or data scientist, I encourage you to take DataCamp’s Introduction to SQL course to understand the basics of SQL functions in data transformation. You should also check out the SQL Basics Cheat Sheet to review some foundational concepts as we explore the topic further.

The Quick Answer: How to Use SQL REPLACE()

The SQL REPLACE() function alters or substitutes all string occurrences with a specified substring or string. In the example below, the REPLACE() function in SQL has replaced the value old with new in the description column. The query will execute each row to replace the old string with a new one.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Note: MySQL, PostgreSQL, Oracle, and SQL Server databases all support the REPLACE() function.

Understanding the SQL REPLACE() Function

The REPLACE() function in SQL searches for the specified substring or string in a column and replaces it with another given string. The example below shows the basic use of the REPLACE() function. The arguments in the function include:

  • string: The original string where you are supposed to make the replacement.
  • old_substring: The substring to be replaced.
  • new_substring: The substring to replace the old one.
REPLACE(string, old_substring, new_substring)

When using the REPLACE() function to alter the strings in a table, the query will have the following syntax where:

  • column_name: The column used to search the substring occurrences.
  • old_substring: The substring to be replaced.
  • new_substring: The substring to replace the old one.
-- 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;

Detailed Examples and Use Cases

There are many important use cases of the SQL REPLACE() function in text manipulation and data transformation. They include the following:

Basic replacement

You can use the SQL REPLACE() function to perform a basic replacement. Let's assume we have the following table in our SQL Server database:

Products table example in SQL database.Products table example. Image by Author.

Using the following SQL query, you can use the REPLACE() function to change the old status to new.

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

The resulting table will look like the one shown below.

Example of Products table updated using the basic SQL REPLACE function

Table updated with SQL REPLACE(). Image by Author.

Replacing multiple substrings

If you want to replace multiple substrings, use chained multiple REPLACE() functions. Here, we are updating the value old with outdated, and new with 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;

The resulting table is shown below.

Example of Products table updated using multiple substrings of SQL REPLACE function.

Table updated using multiple substrings. Image by Author.

Handling case sensitivity

Since the REPLACE() function transforms string data types, it is case-sensitive and will only modify the substrings that match the exact case of old_substring. Therefore, it is important to note the original case values before using the REPLACE() function.

When you want to replace values that are not case-sensitive, you should use the UPPER() or LOWER() functions to convert the text before replacement. This technique ensures consitency. 

-- 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;

Example output of table updated using the SQL REPLACE and UPPER functions.

Table updated using the REPLACE() and UPPER() functions. Image by Author.

-- 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;

Example output of table updated using the SQL REPLACE and LOWER functions.

Table updated using the SQL REPLACE() and LOWER() functions. Image by Author.

Advanced Techniques

Let's now take a look at some even more advanced techniques involving REPLACE().

Replacing with dynamic values

The REPLACE() function can be used to perform dynamic replacements by using a combination of other SQL functions. For example, using the REPLACE() function, you can use the value of another column to replace the substring. In the example below, the REPLACE() function replaces each old product with the actual value from 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;

Example output of table updated using SQL REPLACE function and dynamic values.

Table updated using REPLACE() function and dynamic values. Image by Author.

Using REPLACE() in UPDATE statements

The SQL REPLACE() function is used within the UPDATE function to modify the values in a table. The UPDATE function mainly changes a table's columns or values. For example, the query below will update the value old with outdated in the column status.

-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Combining REPLACE() with other string functions

One advanced technique for using the REPLACE() function is combining other string functions, such as CHARINDEX() or SUBSTRING(), when replacing string values.

We can enhance the REPLACE() function by combining it with other string functions, such as CHARINDEX() or SUBSTRING(), for more effective string manipulation.

-- 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;

The resulting table from the above query is shown below.

Example output of table updated using REPLACE and other string functions.

Table updated using REPLACE() and other string functions. Image by Author.

Replacing NULL with 0

You can use the COALESCE() function to replace the NULL value with 0, as you can see in the query below.

-- 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;

Practical Applications of the SQL REPLACE() Function

Now that we have explored how to use REPLACE(), let's consider some of the practical applications.

  • Data Cleaning: The REPLACE() function is useful for data cleaning, removing, and replacing unwanted characters or spaces in strings within a dataset.
  • Data Transformation: The REPLACE() function is used in data transformation to change values into different formats and replace outdated values with new values.
  • Handling User Inputs: The REPLACE() function is important in adjusting and converting user input data to a uniform standard acceptable in a specific database. This technique helps in ensuring uniformity in the database.
  • Text Standardization: The REPLACE() function standardizes values in string columns to ensure uniformity, such as abbreviations in lowercase or uppercase formats.
  • Conditional Replacement: The REPLACE() function is also useful for easy database management practices to replace values according to specific rules.

Database-Specific Implementations and Considerations

MySQL, PostgreSQL, Oracle, and SQL Server databases all support the REPLACE() function in SQL, which has a similar syntax in these databases.

-- Replace 'old' with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Performance Considerations and Best Practices

When using the REPLACE() function, it is important to note the following performance considerations and best practices.

Impact on query performance

The REPLACE() function may impact query performance, especially when handling large datasets. This issue occurs since the function updates the table by each row and may take a while for the query to execute.

Indexing considerations

It is important to ensure proper indexing of the columns for optimal performance.

Monitoring and resolving performance bottlenecks

To resolve the issues involved with query performance, it is important to consider the following best practices:

  • Use database profiling tools to monitor query performance and optimize when handling large datasets.
  • When using REPLACE() to modify the string values, maintain the indexes regularly to ensure they match.
  • Initiate batch processing to reduce query execution time when handling large datasets.

To learn other advanced SQL functions for data transformation, I recommend taking DataCamp’s Learn SQL course. The Reporting in SQL course also covers topics about database optimization, helping you learn the different techniques to optimize performance.

Conclusion and Further Learning

As we have seen, the REPLACE() function in SQL changes a part of a string with another one. Applying the REPLACE() function is important for different data cleaning and transformation practices. It is important to continue practicing the use and different scenarios where you can apply the REPLACE() function to manipulate and update string data types.

You can learn more about the REPLACE() function in SQL by taking DataCamp’s Intermediate SQL and SQL Fundamentals courses. I also recommend taking the Associate Data Analyst in SQL career track to learn more about SQL's applications in data transformation and jumpstart your career. Finally, I encourage you to take DataCamp’s SQL Associate Certification to help you showcase your professional achievement in using SQL for data analysis and stand out during job interviews.

Finally, if you are interested in learning more about various SQL functions used in data analysis, I recommend checking out some of my other tutorials, including:


Photo of Allan Ouko
Author
Allan Ouko
I create articles that simplify data science and analytics, making them easy to understand and accessible.

Frequently Asked Questions

What is the SQL REPLACE() function?

SQL REPLACE() function replaces or modifies all occurrences of a specified substring with another substring.

Is the REPLACE() function case-sensitive?

The SQL REPLACE() function is case-sensitive. If handling values that are not case-sensitive, you must convert the string using UPPER or LOWER functions before replacement.

Can the REPLACE() function in SQL replace multiple substrings?

If you want to replace multiple substrings, use chained multiple REPLACE() functions in SQL.

Which SQL databases support the REPLACE() function?

All the major databases, including SQL Server, Oracle, PostgreSQL, and MySQL, support the SQL REPLACE() function.

Does the SQL REPLACE() function impact query performance?

The REPLACE() function in SQL may impact query performance when handling large datasets. To improve performance, optimize queries and use proper indexing.

Topics

Learn SQL with DataCamp

Course

Introduction to SQL

2 hr
714.1K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

tutorial

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

4 min

tutorial

Hacking Date Functions in SQLite

In this tutorial, learn how to use date functions in SQLite.
Hillary Green-Lerman's photo

Hillary Green-Lerman

3 min

tutorial

How to Best Use the SQL LIMIT Clause

Learn the application of the SQL LIMIT clause to filter data. Master the use of the LIMIT clause in PostgreSQL and MySQL databases.
Allan Ouko's photo

Allan Ouko

8 min

tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

See MoreSee More