REPLACE(text, old, new) returns text with every occurrence of old replaced by new. It takes exactly three arguments, it works the same way in MySQL, SQL Server, PostgreSQL, SQLite and Oracle, and it does not change your data until you combine it with UPDATE.
Jump to: syntax · sample data · examples · UPDATE with REPLACE · remove or clean characters · count occurrences · edge cases · mistakes · FAQ
REPLACE syntax by database
| Database | Syntax | Notes |
|---|---|---|
| MySQL | REPLACE(str, from_str, to_str) | Case-sensitive match. Not the same as the REPLACE INTO statement |
| PostgreSQL | REPLACE(str, from, to) | Case-sensitive. For patterns use REGEXP_REPLACE; for several single characters use TRANSLATE |
| SQL Server | REPLACE(string, pattern, replacement) | Follows the collation, so the default (case-insensitive) collation matches L and l alike |
| SQLite | REPLACE(X, Y, Z) | Case-sensitive |
| Oracle | REPLACE(char, search, replacement) | Replacement is optional; leaving it out deletes the search text. Oracle also treats an empty string as NULL |
The three arguments are: the text to search, the text to look for, and the text to put in its place. Every match is replaced, not just the first one.
Sample data
Every query below was run on SQLite 3.45 and the output is copied from the real result. The same queries work unchanged in MySQL, PostgreSQL, SQL Server and Oracle (the one exception is the newline example, which uses char(10); see the note there).
CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT, notes TEXT);
INSERT INTO contacts VALUES
(1, 'Ana Torres', '(415) 555-0142', '[email protected]', 'Likes red, red wine'),
(2, 'Ben Okafor', '212.555.0199', '[email protected]', 'Call after 5pm'),
(3, 'Chloe Dupont', '303-555-0111', '[email protected]', 'Line one' || char(10) || 'Line two'),
(4, 'Dev Patel', '512 555 0170', NULL, 'VIP');Examples
1. The basics
SELECT REPLACE('Hello World', 'World', 'SQL') AS swapped,
REPLACE('banana', 'a', 'o') AS every_match,
REPLACE('a1b2c3', '1', '') AS deleted;swapped | every_match | deleted
Hello SQL | bonono | ab2c3Notice banana became bonono: all three a characters changed. Replacing with an empty string '' deletes the match, which is how you remove characters.
2. Fix a changed email domain (in a SELECT)
Preview the change before you make it. REPLACE in a SELECT never touches the table:
SELECT email, REPLACE(email, 'old-acme.com', 'acme.com') AS fixed
FROM contacts;email | fixed
[email protected] | [email protected]
[email protected] | [email protected]
[email protected] | [email protected]
NULL | NULLRows that do not contain the search text come back unchanged, and a NULL stays NULL.
3. Replace text in a column permanently
Once the preview looks right, run it as an UPDATE. Always include a WHERE so you only touch the rows that need it (see below).
4. Strip punctuation from phone numbers
To remove several different characters, nest one REPLACE inside another. The innermost runs first:
SELECT phone,
REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', '') AS digits
FROM contacts;phone | digits
(415) 555-0142 | 4155550142
212.555.0199 | 212.555.0199
303-555-0111 | 3035550111
512 555 0170 | 5125550170The second row still has dots because we never told it to remove .. Nesting only cleans what you list, so check the result for characters you missed. This is the answer to "sql replace multiple characters". PostgreSQL has a tidier option, TRANSLATE(phone, '()-. ', ''), and PostgreSQL, MySQL 8, Oracle and SQL Server 2025 offer regular expressions (REGEXP_REPLACE) for anything more complex.
5. Collapse double spaces
SELECT notes, REPLACE(notes, ' ', ' ') AS cleaned
FROM contacts WHERE id = 2;notes | cleaned
Call after 5pm | Call after 5pmEach pass turns two spaces into one, so a run of four or more spaces needs more than one pass: wrap the call in itself (REPLACE(REPLACE(notes, ' ', ' '), ' ', ' ')) until it is clean. To trim only the ends, use TRIM instead, which is what you want for leading and trailing spaces.
6. Replace a newline
Line breaks are invisible characters, so you replace them by code. In SQLite and PostgreSQL that is char(10) (PostgreSQL: chr(10)), in SQL Server CHAR(10), and in MySQL CHAR(10) or '\n'. Windows data often uses CHAR(13) + CHAR(10), so remove 13 first.
SELECT id, REPLACE(notes, char(10), ' / ') AS flat
FROM contacts WHERE id = 3;id | flat
3 | Line one / Line two7. Use REPLACE in a WHERE clause
You can compare against the cleaned value. This matches phone numbers however they are formatted:
SELECT name FROM contacts
WHERE REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '.', '') LIKE '212%';name
Ben OkaforFunctions on a column stop the database using an index on it, so this is fine for one-off cleanup but slow on large tables. Better to clean the data once and store the clean version.
UPDATE with REPLACE
REPLACE on its own only changes what a query returns. To change the stored data, assign the result back in an UPDATE:
UPDATE contacts
SET email = REPLACE(email, '@old-acme.com', '@acme.com')
WHERE email LIKE '%@old-acme.com';SELECT id, email FROM contacts;
id | email
1 | [email protected]
2 | [email protected]
3 | [email protected]
4 | NULLThree habits keep this safe:
- Preview with SELECT first (example 2) and check the row count.
- Always add a WHERE. Without one, every row is rewritten. Here it also keeps rows that do not contain the text from being needlessly updated.
- Run it in a transaction (
BEGIN; UPDATE ...;check the result, thenCOMMIT;orROLLBACK;) so you can undo a mistake. Back up the table first if the data matters.
Remove or clean characters
There is no separate "remove" function. Replacing with '' is removal, as in example 1. For the common cases, pick the right tool:
| Goal | Use |
|---|---|
| Delete a known character or word | REPLACE(col, 'x', '') |
| Trim spaces from the ends | TRIM(col) |
| Swap one set of characters for another | TRANSLATE (PostgreSQL, Oracle, SQL Server 2017+) |
| Match a pattern (digits, whitespace runs) | REGEXP_REPLACE (PostgreSQL, MySQL 8, Oracle, SQL Server 2025) |
| Replace by position instead of by text | STUFF (SQL Server) or OVERLAY (PostgreSQL); see SUBSTRING |
Bonus: count how many times text appears
Compare the length of the string before and after removing the search text, then divide by the length of the search text:
SELECT name,
(LENGTH(notes) - LENGTH(REPLACE(notes, 'red', ''))) / LENGTH('red') AS red_count
FROM contacts WHERE id = 1;name | red_count
Ana Torres | 2Use LEN instead of LENGTH in SQL Server (and note it ignores trailing spaces).
Edge cases worth knowing
Run on SQLite 3.45; other databases differ at the margins.
SELECT REPLACE('Hello', 'L', 'x'), -- case differs
REPLACE('abc', '', 'x'), -- empty search text
REPLACE(NULL, 'a', 'b'), -- NULL text
REPLACE('abc', 'b', NULL); -- NULL replacementHello | abc | NULL | NULL- Case matters in SQLite, MySQL and PostgreSQL.
REPLACE('Hello', 'L', 'x')found no capitalLand changed nothing. SQL Server follows the collation, and its default collation is case-insensitive. - An empty search string changes nothing (SQLite, MySQL, PostgreSQL), and SQL Server returns the original text too.
- NULL in, NULL out. A NULL anywhere in the arguments gives NULL. Wrap with COALESCE if you need a fallback.
- Replacements do not cascade. The search text is matched against the original string, so replacing
awithaawill not loop forever.
Common mistakes
- Forgetting the UPDATE.
SELECT REPLACE(...)shows a result but the table is unchanged. - UPDATE without WHERE. Every row is rewritten. Preview first and use a transaction.
- Confusing REPLACE with REPLACE INTO. In MySQL and SQLite,
REPLACE INTOis a statement that inserts or overwrites a row. It has nothing to do with the string function. - Expecting case-insensitive matching. Wrap both sides in
LOWER()if you need that, or use a regex with an ignore-case flag. - Replacing too much.
REPLACE(email, 'com', 'org')also rewritescominsidecompany.net. Include enough context ('@old-acme.com') to match only what you mean. - Expecting wildcards.
REPLACEmatches literal text.%and_mean nothing here (unlike LIKE). UseREGEXP_REPLACEfor patterns. - Nesting too deeply. Five or six nested calls are unreadable. Switch to
TRANSLATEorREGEXP_REPLACE, or clean the data in stages with a CTE.
FAQ
What does REPLACE do in SQL?
It returns a copy of a string with every occurrence of one piece of text swapped for another. REPLACE('Hello World', 'World', 'SQL') returns Hello SQL.
How do I replace text in a column in SQL?
Use UPDATE table SET col = REPLACE(col, 'old', 'new') WHERE col LIKE '%old%';. Preview with a SELECT first.
Does SQL REPLACE replace all occurrences?
Yes. Every match in the string is replaced, not only the first one.
Is SQL REPLACE case-sensitive?
It depends on the database and collation. SQLite, MySQL and PostgreSQL are case-sensitive; SQL Server follows the column's collation, which is case-insensitive by default.
How do I replace multiple characters in SQL?
Nest REPLACE calls, use TRANSLATE for one-to-one character swaps, or use REGEXP_REPLACE for patterns.
How do I remove a character from a string in SQL?
Replace it with an empty string: REPLACE(col, '-', '').
What is the difference between REPLACE and TRANSLATE?
REPLACE swaps one whole substring for another. TRANSLATE swaps individual characters one-for-one, so it is shorter when you have many single characters to change.
What is the difference between REPLACE and STUFF?
REPLACE works by matching text. STUFF (SQL Server) works by position and length, like a delete plus an insert.
Practice
Try the free interactive string function lesson in your browser, then work through the SQL practice problems. REPLACE pairs naturally with SUBSTRING and INSTR for slicing text, and with duplicate removal when messy formatting hides matching rows. The SQL cheat sheet lists the other string functions, and there are database-specific guides for MySQL and T-SQL.