Beginner

57. REPLACE Function

Learn how to replace one text value or pattern with another value in SQL.

📘 Lesson 57 of 728 ⏱ 7 min 🎯 Beginner

The REPLACE function searches a string for a specified text pattern and replaces matching occurrences with another text value. It is commonly used for text cleaning and transformation.

Imported or manually entered data can contain inconsistent characters, abbreviations, or unwanted text. REPLACE allows analysts to transform those values into a more consistent format.

If a dataset uses 'Mum' in some records and 'Mumbai' in others, REPLACE can be used to transform text values when appropriate.

SQL 
SELECT
    REPLACE(City, 'Mum', 'Mumbai') AS CityName
FROM Customers;

REPLACE is useful for cleaning addresses, standardizing categories, correcting text patterns, transforming codes, and preparing data for reporting.

  • REPLACE changes matching text.
  • It searches for a specified pattern.
  • It substitutes the matching pattern with new text.
  • It is useful for data cleaning.
  • The original table is not changed unless REPLACE is used inside an UPDATE statement.
What does REPLACE do in SQL?

REPLACE searches for specified text and substitutes matching occurrences with another text value.

Does SELECT with REPLACE permanently change the data?

No. Using REPLACE in SELECT only changes the displayed result. Permanent changes require a data modification statement such as UPDATE.

Have you finished this lesson?

Introduction to SQL – All Lessons