On this page
Syntax
REPLACE(string, from_substring, to_substring)REPLACE(column, ' ', '')REPLACE(REPLACE(column, 'a', 'b'), 'c', 'd')UPDATE t SET col = REPLACE(col, from_substring, to_substring) | Parameter | Type | Required | Description |
|---|---|---|---|
string |
string or column | yes | The source value that is scanned. It can be a literal, a column, or any expression that yields text. |
from_substring |
string | yes | The substring to look for. Every occurrence is replaced, not only the first one. |
to_substring |
string | yes | The text that each occurrence is swapped for. Pass an empty string to delete the search substring outright. |
How it works
REPLACE() takes three arguments: the text to work on, the substring to find, and the substring to put in its place. It walks the whole value and swaps every match, then hands back a brand new string. The stored data is never touched unless you wrap the call in an UPDATE, so a plain SELECT REPLACE(...) is completely safe to experiment with.
The most common jobs are stripping unwanted characters and changing delimiters. Passing an empty string as the third argument deletes the search text, so REPLACE(phone, ' ', '') removes every space. Passing a different delimiter reformats a value, so you can turn a value that uses one separator into one that uses another. It pairs well with SUBSTRING() when you need to change text by position rather than by content.
On MySQL, SQL Server, and SQLite the match is case sensitive by default, so REPLACE('Hello', 'h', 'J') leaves the value untouched because the capital H does not match a lowercase h. PostgreSQL behaves the same way. When you only want to clean the two ends of a value rather than its interior, reach for TRIM() instead, and browse the full SQL functions library for related tools.
Examples
Remove all spaces from a value
/* an empty replacement deletes every space */
SELECT REPLACE(' John Smith ', ' ', '') AS cleaned;cleaned ========= JohnSmith
Swap one delimiter for another
/* reformat a phone number by swapping - for / */
SELECT REPLACE('971-50-123-4567', '-', '/') AS reformatted;reformatted =============== 971/50/123/4567
Mask part of a string
/* hide a known email domain behind stars */
SELECT email,
REPLACE(email, '@example.com', '@*****.com') AS masked
FROM users;email | masked ==================+================== sara@example.com | sara@*****.com omar@example.com | omar@*****.com
Chain REPLACE calls to strip several characters
/* peel away spaces, dots, and slashes in one pass */
SELECT REPLACE(REPLACE(REPLACE('971 50.123/4567', ' ', ''), '.', ''), '/', '') AS digits_only;digits_only ============ 971501234567
Fix stored data with an UPDATE
/* turn every space in a code into an underscore */
UPDATE products
SET sku = REPLACE(sku, ' ', '_')
WHERE sku LIKE '% %';sku (before) | sku (after) ==============+============= AB 100 X | AB_100_X CD 200 Y | CD_200_Y
Delete a fixed prefix from every value
/* drop the leading protocol from stored urls */
SELECT REPLACE(url, 'https://', '') AS host_path
FROM links;host_path ====================== codewithsql.com/blog codewithsql.com/learn
Common mistakes
/* you expected only the first space to change,
but REPLACE swaps every one of them */
SELECT REPLACE('a b c', ' ', '_');/* REPLACE always changes every match. If you truly
need only the first one, split the value with
SUBSTRING and reassemble it, or use a regex
function such as REGEXP_REPLACE with a limit. */
SELECT REPLACE('a b c', ' ', '_') AS every_match; /* a_b_c */The number one surprise with REPLACE() is that it is greedy: it replaces all occurrences of the search substring, never just the first. Plain REPLACE has no way to target a single match.
/* nothing changes: the capital H does not match */
SELECT REPLACE('Hello World', 'h', 'J');/* match the exact case, or normalise first */
SELECT REPLACE('Hello World', 'H', 'J') AS fixed; /* Jello World */
/* case insensitive: fold the value first */
SELECT REPLACE(LOWER('Hello'), 'h', 'j');On MySQL, PostgreSQL, SQL Server, and SQLite the search is case sensitive by default. If you need a case insensitive swap, normalise the text with string functions first or use a regex based replace.
/* trying to trim outer spaces, but this also
destroys the space between the words */
SELECT REPLACE(' hi there ', ' ', '');/* TRIM removes only the leading and trailing
characters and leaves the interior intact */
SELECT TRIM(' hi there ') AS trimmed; /* hi there */Use TRIM() when you only want to clean the two ends of a value. REPLACE touches the whole string, so it will happily strip spaces that sit between words as well.
Performance
REPLACE() runs once per row and reads the whole value each time, so on a normal SELECT it is inexpensive. The cost only becomes noticeable when you wrap it around a column inside a WHERE clause, because that hides the raw column from the optimiser and forces a full scan. If you frequently filter on a cleaned form of a value, store the cleaned form in its own column or in a generated column instead of computing it on every query.
Chaining several REPLACE() calls is fine for a handful of characters, but each layer adds another pass over the string. When you need to strip or translate many single characters at once, PostgreSQL and Oracle offer TRANSLATE(), which maps a whole set of characters in one call and is easier to read than a deep nest of replaces.
Bulk cleanups with an UPDATE ... SET col = REPLACE(col, ...) rewrite every matching row and generate write ahead log, so run them in batches on large tables and add a WHERE that skips rows already in the target shape. See the indexing guide for why filtering on a function result cannot use an ordinary index.
Interview questions
What does the SQL REPLACE function do, and how many matches does it change?
REPLACE(string, from, to) returns a copy of the first argument in which every occurrence of the from substring has been swapped for the to substring. It is greedy: it always replaces all matches, never just the first one.
Is REPLACE case sensitive?
Yes, by default on MySQL, PostgreSQL, SQL Server, and SQLite the search substring must match the exact case in the source. To do a case insensitive replace you normalise the text first, for example with LOWER, or you use a regular expression based replace function.
How do you remove every space from a column with REPLACE?
Pass an empty string as the replacement: REPLACE(column, ' ', ''). An empty third argument means each match is deleted rather than swapped, so all spaces disappear.
SELECT REPLACE(product_code, ' ', '') AS cleaned
FROM products;What is the difference between REPLACE and TRIM?
TRIM() only removes characters from the two ends of a value, while REPLACE swaps a substring wherever it appears in the whole string. Use TRIM to clean leading and trailing padding, and REPLACE to change or delete content in the interior.
How would you replace several different substrings in one query?
Nest the calls so each REPLACE feeds the next, for example REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd'). When you need to map many single characters at once, PostgreSQL and Oracle offer TRANSLATE(), which handles a whole set in a single call.
SELECT REPLACE(REPLACE(phone, ' ', ''), '.', '') AS digits
FROM contacts;