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.

SQL REPLACE() - Setup for Examples

Examples: Using REPLACE() in SQL Queries

We’ll go through examples demonstrating the REPLACE() function in SQL, using sample data from a products table with fields product_name and description.

SQL REPLACE() - products table for examples

1. Replacing a Word in Product Descriptions

To replace the word “Old” with “New” in the description column:

</>
Copy
SELECT product_name, REPLACE(description, 'Old', 'New') AS updated_description
FROM products;

This query returns the description for each product with “Old” replaced by “New”. This can be helpful when updating terms or names in large data sets.

SQL REPLACE() Example - Replacing a Word in Product Descriptions

2. Replacing Spaces with Hyphens in Product Names

To replace spaces with hyphens in the product_name column, creating a URL-friendly format:

</>
Copy
SELECT product_name, REPLACE(product_name, ' ', '-') AS url_friendly_name
FROM products;

This query returns the product_name with spaces replaced by hyphens, making it suitable for URLs or file names.

SQL REPLACE() Example - Replacing Spaces with Hyphens

3. Removing Unwanted Characters Using REPLACE()

To remove all instances of the character “@” from the description column:

</>
Copy
SELECT product_name, REPLACE(description, '@', '') AS clean_description
FROM products;

This query returns the description with all “@” symbols removed, useful for data cleanup.

SQL REPLACE() Example - Removing Unwanted Characters

4. Using REPLACE() to Standardize Case or Format

To replace the abbreviation “oz” with “ounces” in the description column:

</>
Copy
SELECT product_name, REPLACE(description, 'oz', 'ounces') AS standardized_description
FROM products;

This query returns the description with “oz” replaced by “ounces”, standardizing measurement units across product descriptions.

SQL REPLACE() Example - Using REPLACE() to Standardize Format

FAQs for SQL REPLACE()

1. What does the SQL REPLACE() function do?

The REPLACE() function searches for a specified substring within a string and replaces it with another substring.

2. Can REPLACE() handle NULL values?

If the input string or the substring is NULL, REPLACE() returns NULL.

3. Is REPLACE() case-sensitive?

Yes, REPLACE() is case-sensitive in SQL Server and MySQL, so “abc” and “ABC” are treated as different strings.

4. Can REPLACE() be used with numeric data?

REPLACE() works only with string data. To use it with numbers, you must first convert the number to a string.

5. Does REPLACE() modify the original data in the table?

No, REPLACE() does not modify the original data in the table. To update the data, use it within an UPDATE statement.