How to Use the SQL REPLACE Function
Working with text data in SQL often requires cleaning, transforming, or modifying strings. One of the most commonly used string functions for these tasks is REPLACE. The REPLACE function allows you to substitute all occurrences of a specific substring with another substring, directly within your SQL queries—making it especially valuable for data cleaning and transformation operations in a database environment.
In this article, we will explore the REPLACE function in SQL in depth. We will cover its syntax, demonstrate use cases with examples, examine how it behaves with case sensitivity, and discuss common applications.
Learn SQL online
What is the REPLACE Function?
The REPLACE function is a scalar string function provided by most relational database systems, including SQL Server, MySQL, PostgreSQL (as a synonym for REPLACE or REGEXP_REPLACE), Oracle, and others.
It allows you to search for a specific sequence of characters (a substring) within a larger string and replace every instance of that substring with a different sequence of characters.
This function is useful when:
You need to correct data entry issues
You want to strip out unwanted characters (e.g., dashes or slashes in phone numbers)
You’re performing data standardization or normalization
You’re updating formatting in a string (e.g., replacing one delimiter with another)
Syntax
REPLACE(string_expression, search_string, replacement_string)
string_expression: The original string you want to modify.search_string: The substring you want to find and replace.replacement_string: The substring to replacesearch_stringwith.
This function returns a new string with all occurrences of search_string replaced by replacement_string.
Basic Example: Replacing a Word
Let’s begin with a very simple example to illustrate the behavior of the function:
SELECT REPLACE('Hello World', 'World', 'SQL') AS Result;
Output:
In this case:
"World"is found in the string"Hello World".It is replaced with
"SQL".The final result is
"Hello SQL".
Example with Table Data
Let’s imagine we’re working with a table named Customers that looks like this:
| CustomerID | FullName |
|---|---|
| 1 | Mr. John Smith |
| 2 | Mr. Alex Johnson |
| 3 | Ms. Sarah Connor |
Suppose we want to remove honorifics like “Mr.” and “Ms.” from the FullName column. We can achieve this by chaining REPLACE functions:
SELECT
CustomerID,
REPLACE(REPLACE(FullName, 'Mr. ', ''), 'Ms. ', '') AS CleanName
FROM Customers;
Output:
| CustomerID | CleanName |
|---|---|
| 1 | John Smith |
| 2 | Alex Johnson |
| 3 | Sarah Connor |
We first replace
'Mr. 'with an empty string''.Then, on the result of that, we replace
'Ms. 'with an empty string.This effectively removes the titles from all full names.
Data Cleaning Example: Phone Numbers
Another practical use case is cleaning up messy data. Consider a table with phone numbers stored in inconsistent formats:
| ContactID | PhoneNumber |
|---|---|
| 1 | 123-456-7890 |
| 2 | (123) 456-7890 |
| 3 | 123.456.7890 |
You want to standardize the numbers by removing all punctuation.
SELECT
ContactID,
REPLACE(
REPLACE(
REPLACE(
PhoneNumber,
'-', ''),
'.', ''),
'(', '') AS CleanedNumber
FROM Contacts;
You could nest even more REPLACE calls to also remove ')', spaces, or other characters as needed.
Use Cases Recap
Here are just a few real-world scenarios where REPLACE comes in handy:
| Use Case | Description |
|---|---|
| Remove prefixes | Strip honorifics like Mr./Ms./Dr. from names |
| Clean formatting | Remove or standardize dashes, parentheses, dots from phone numbers |
| Fix typos | Replace common spelling errors or aliases |
| Change delimiters | Replace commas with pipes or semicolons in CSV-style fields |
| Normalize strings | Swap abbreviations, standardize brand or product names |
Summary
The SQL REPLACE function is an indispensable tool for anyone working with text data. It allows you to clean, format, and manipulate string values efficiently—all within your SQL queries.
Key Points to Remember:
REPLACE(original_string, search, replace)replaces all occurrences ofsearchwithreplace.It is not regex-based—it works on literal string matching.
Nesting multiple
REPLACEcalls is common when replacing multiple substrings.Case sensitivity depends on the database’s collation settings.
Ideal for string cleanup, formatting corrections, and standardization tasks.
If you’re building ETL pipelines, preparing reports, or managing messy data, mastering REPLACE will make your SQL queries cleaner, faster, and more maintainable.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
