REPLACE TEXT Using SQL REPLACE Function

There are many situations where you need to modify text values like correct typos, standardize formats, or clean up messy data etc. Wwen working with databases. That’s where the SQL REPLACE() function becomes incredibly useful.

In this POST, you’ll learn how to use the SQL REPLACE() function, see practical examples, and understand how it works in SQL database systems.

Getting Started

In SQL, replacing text inside a string is done using the simple and powerful REPLACE() function.

The SQL REPLACE() function is a simple yet powerful tool for manipulating text data. Whether you're fixing inconsistencies, formatting strings, or cleaning datasets, mastering this function can significantly improve your database operations.

What is the SQL REPLACE Function?

The REPLACE() function in SQL is used to substitute all occurrences of a specified substring within a string with another substring.

Syntax:

  
  REPLACE(original_string, search_string, replacement_string)
  

Parameters:

• original_string → The text you want to modify
• search_string → The substring you want to replace
• replacement_string → The new substring to insert

Example:

  
  SELECT REPLACE('apple apple apple', 'apple', 'orange');
  

Output:

  
  orange orange orange
  

How It Actually Works

• Scans the entire string
• Finds all occurrences of the search pattern
• Replaces them non-conditionally
• It does not stop after the first match, every match is replaced

Basic Example of SQL REPLACE

Replace text inside a string

  
  SELECT REPLACE('Hello World', 'World', 'SQL') AS Result;
  

Output:

  
  Hello SQL
  

Replace Table Data

  
  SELECT name, REPLACE(city, 'Bombai', 'Mumbai') AS updated_city
  FROM customers;
  

Updating Data:

  
  UPDATE customers
SET city = REPLACE(city, 'Bombai', 'Mumbai');
  

Multiple Replacements:

  
  SELECT REPLACE(REPLACE('abc def ghi', 'abc', '123'), 'def', '456') AS Result;
  

Key Points to Remember

• Replaces all occurrences of the substring
• Case sensitivity depends on the database
• Can be used in both SELECT and UPDATE statements
• Useful for data transformation and cleanup

Summary

The SQL REPLACE() function is more than just a simple string tool,it’s a core utility for data cleaning, transformation, and normalization. But like any powerful function, it should be used carefully especially when working with large datasets or production systems.

Thanks