SQL REPLACE

What is the SQL REPLACE function?

REPLACE returns a new string in which all occurrences of a search substring are substituted with a replacement substring.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REPLACE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift

SQL REPLACE Full Explanation

REPLACE is a scalar string function defined by the SQL standard. It scans an input string for every occurrence of a search substring and substitutes each match with a replacement substring, returning the transformed value. The operation is case-sensitive or case-insensitive depending on the database collation.Key points:- Non-destructive: REPLACE does not alter data in place; it only returns a modified result. To persist changes, wrap it in UPDATE.- Global substitution: All occurrences are replaced, not just the first.- Null handling: If any argument is NULL, the function returns NULL.- Data types: Arguments must be character types (CHAR, VARCHAR, TEXT, etc.). Some systems implicitly cast numbers to strings.- Collation awareness: In case-insensitive collations, 'ABC' and 'abc' are treated the same for matching.- Multibyte safety: Most databases operate on characters, not bytes, so UTF-8 strings are handled correctly.- Performance: On large text columns, REPLACE may force a full scan and produce large temporary values; index use is limited.

SQL REPLACE Syntax

REPLACE(source_string,
        search_substring,
        replacement_substring)

SQL REPLACE Parameters

source_string (STRING) - The original text to search within
search_substring (STRING) - The text to find. An empty string returns source_string unchanged.
replacement_substring (STRING) - The text that will replace each occurrence.

Example Queries Using SQL REPLACE

-- Replace domain in an email column for all users
SELECT REPLACE(email, '@oldcorp.com', '@newcorp.com') AS new_email
FROM   users;

-- Remove dashes from phone numbers
SELECT REPLACE(phone, '-', '') AS digits_only
FROM   contacts;

-- Persist the change in a table
UPDATE products
SET    description = REPLACE(description, 'colour', 'color')
WHERE  description LIKE '%colour%';

Expected Output Using SQL REPLACE

Each query returns a result set (or performs an update) where every matching substring has been substituted by the replacement substring

Use Cases with SQL REPLACE

Normalizing data during ETL pipelines.
Cleaning user-entered text such as phone numbers or URLs.
Migrating branding terms or domain names across large text fields.
Removing unwanted characters before analytics processing.

Common Mistakes with SQL REPLACE

Expecting REPLACE to update the table automatically without an UPDATE statement.
Forgetting that NULL in any argument yields NULL.
Assuming only the first occurrence is replaced.
Misinterpreting case sensitivity due to database collation settings.

First Introduced In

SQL-92 standard

Frequently Asked Questions

Does REPLACE update rows automatically?

No. It only returns a transformed value. Use an UPDATE statement to store the result back into the table.

Is REPLACE faster than REGEXP_REPLACE?

Yes, because it performs simple literal matching. Regular-expression functions add parsing overhead and should be used only when pattern matching is required.

Can I remove characters with REPLACE?

Absolutely. Specify an empty string ('') as the replacement to delete all occurrences of the search substring.

Why is my query returning NULL?

If any of source_string, search_substring, or replacement_substring is NULL, the output is NULL. Ensure none of the inputs are NULL before calling REPLACE.