REPLACE() in SQL

Replaces all occurrences of a substring within a string with another substring.

Syntax

REPLACE(original_string, search_string, replacement_string)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

REPLACE() Function Example

SELECT REPLACE('Hello World', 'World', 'SQL');
-- Output: 'Hello SQL'

What is REPLACE() in SQL?

The REPLACE() function in SQL replaces all occurrences of a specified substring within a given string with another substring. It is commonly used to clean up text, modify values dynamically, or standardize formatting in database records. This function is supported in most SQL databases, including MySQL, PostgreSQL, SQL Server, and SQLite, though some systems have slight variations in syntax. Unlike SUBSTRING() or TRIM(), REPLACE() does not modify the original data but returns a new string with the specified replacements applied. It is useful for updating text fields, correcting typos, and formatting outputs efficiently.

Parameters:

original_string: The string to perform replacements in
search_string: The substring to find and replace
replacement_string: The string to replace occurrences with

Example Use Cases:

-- Clean phone numbers

SELECT REPLACE(phone_number, '-', '') AS clean_number 
FROM contacts;
-- Output:
phone_number        clean_number
-----------------   -------------
555-123-4567       5551234567
123-555-7890       1235557890
888-555-1234       8885551234
777-123-5555       7771235555
444-555-8888       4445558888

-- Format URLs

SELECT REPLACE(url, 'http://', 'https://') AS secure_url 
FROM websites;
-- Output:
url                             secure_url
------------------------------  ------------------------------
http://example.com              https://example.com
http://mysite.org              https://mysite.org
http://testsite.net            https://testsite.net
http://company.com/about       https://company.com/about
http://blog.site.com           https://blog.site.com

-- Standardize text

SELECT REPLACE(description, 'colour', 'color') AS us_english 
FROM products;
-- Output:
description                     us_english
------------------------------  ------------------------------
Vibrant colour display         Vibrant color display
Multiple colour options        Multiple color options
Natural colour tones          Natural color tones
Colour calibrated screen      Color calibrated screen
High-quality colour print     High-quality color print

Notes:

Behavior: Replaces all occurrences of the search string, is case-sensitive by default, and removes the search string if replacement_string is empty ('').
Performance Considerations: Multiple replacements can impact performance
Version Info: Core SQL function, supported in all major DBMS