SQL REPLACE()

The SQL REPLACE() function is used to search for a specified substring within a string and replace it with another substring. This function is useful for cleaning or modifying data by replacing characters, words, or symbols with desired values.

The REPLACE() function is available in SQL Server, MySQL, PostgreSQL, and other SQL databases, making it widely applicable for data manipulation tasks.

In this tutorial, we will go through SQL REPLACE() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL REPLACE() Function

The basic syntax of the SQL REPLACE() function is:

</>
Copy
REPLACE(string, substring_to_replace, replacement);

Each part of this syntax has a specific purpose:

  • string: The original string in which you want to search and replace.
  • substring_to_replace: The substring that you want to search for in the original string.
  • replacement: The substring to replace the specified substring with.

The REPLACE() function returns a new string with all instances of substring_to_replace replaced by replacement.

Setup for Examples: Creating the Database and Table

We’ll create a sample products table with fields product_name and description to demonstrate the REPLACE() function examples.

1. First, create a new database called inventory:

</>
Copy
CREATE DATABASE inventory;

2. Select the inventory database to work with:

</>
Copy
USE inventory;

3. Create a table named products with fields product_id, product_name, and description:

</>
Copy
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    description TEXT
);

4. Insert sample data into the products table to use with the REPLACE() function examples:

</>
Copy
INSERT INTO products (product_name, description)
VALUES 
('Laptop Pro', 'High-performance laptop with 16GB RAM and 512GB SSD. Available in Old model and New model.'),
('Wireless Mouse', 'Compact wireless mouse with ergonomic design. Available in 2 oz weight and multiple colors.'),
('Headphones', 'Noise-cancelling headphones with @enhanced bass and @adjustable headband.'),
('Smartphone XL', '6.5-inch display smartphone with 64GB storage in Black color');

With this setup complete, we can run the REPLACE() function examples to test and view results in the products table.