REPLACE in SQL Is Two Different Things

Same word, two unrelated jobs

Search "replace in sql" and you'll get results for two completely different features that happen to share a keyword. REPLACE(string, old, new) is a string function: give it text, it hands back the text with one substring swapped for another. REPLACE INTO is a MySQL and SQLite statement that has nothing to do with strings at all, it deletes and re-inserts an entire row. If you came here looking for "update a row if it exists, insert it if it doesn't," you want that second thing, and it doesn't even exist in SQL Server or DuckDB under that name. If you came here to swap text inside a column, here's the function:

REPLACE takes the string to search, the substring to find, and the substring to put in its place, and returns a new string with every match swapped. That covers the common case. The parts that go wrong are in the details below, and the row-replacing statement with the same name is at the bottom.

It replaces every match, not the first one

There's no "replace once" mode built into REPLACE. Every occurrence goes:

All three instances of 2023 become 2024. If you only wanted the first one changed, REPLACE cannot do that on its own, you need a different function, covered further down.

The silent no-op: case sensitivity

REPLACE matches the search substring byte for byte. It does not lowercase anything first, so a case mismatch doesn't error, it just quietly does nothing.

"Standing Desk" and "Desk Lamp" both contain "Desk", capitalized, but the search string here is 'desk', lowercase. Nothing matches, and every row comes back unchanged, no error, no warning. This is the most common reason REPLACE "doesn't work": the query runs fine and returns the exact input you gave it, which looks like a bug in the function when it's actually an exact match that never happened.

Match the case and it works. If you don't know or don't control the casing of the data, wrap both sides in LOWER() before comparing, though note that changes the casing of the whole output too, so you'd typically do that for a comparison rather than for the value you actually keep.

NULL in, NULL out

REPLACE is a plain scalar function, and like most of them, one NULL argument makes the whole result NULL, not an error and not the untouched string.

All three columns come back NULL. In practice this bites people cleaning a column that has some missing values: the rows with data get fixed, the rows with NULL silently stay NULL instead of erroring, and a downstream COUNT or filter can undercount without anyone noticing. Guard the column with COALESCE first if a missing value should become an empty string instead of staying NULL. The NULL handling lesson covers why this propagation rule exists in more depth.

Only the first match: reach for REGEXP_REPLACE, carefully

When you do need to touch just the first occurrence, or need a pattern instead of a literal substring, REGEXP_REPLACE is the usual next step. But its default is the mirror image of REPLACE's: REPLACE always changes every match, REGEXP_REPLACE changes only the first match unless you pass the 'g' (global) option.

Same input, same search text, and the first query touches only the leading 2023. This trips people up in exactly one direction: they hit REPLACE's all-occurrences behavior, switch to REGEXP_REPLACE for more control, and assume it still replaces everything by default. It doesn't. Also worth knowing if your search text isn't a fixed literal: REGEXP_REPLACE treats its second argument as a pattern, so characters like ., (, or + in the data mean something other than themselves. Wrap a literal search value in REGEXP_ESCAPE() before handing it to REGEXP_REPLACE if it might contain any of those.

The other REPLACE: a statement, not a function

REPLACE INTO looks like it should be related, since it shares the word, but it's an entirely different piece of SQL. MySQL and SQLite support it as shorthand for "delete any row that would conflict with this insert, then insert this row":

REPLACE INTO customers (id, name, email)
VALUES (3, 'Chloe Bennett', '[email protected]');

That's not a string operation on a value, it's a row-level upsert, and it deletes and recreates the whole row rather than updating it in place, which matters if other tables reference that row by foreign key with ON DELETE CASCADE. SQL Server has no REPLACE INTO at all; the closest equivalent there is MERGE. DuckDB doesn't accept the bare MySQL syntax either, its version is spelled out as INSERT OR REPLACE INTO instead. If you searched "t-sql replace" or "ms sql replace" looking for an upsert and landed here, this is the piece you actually want, and the string function above isn't it.

Where to go next

The REPLACE reference page has the full parameter list and return type if you need the quick syntax lookup. REPLACE only matches literal substrings; if you need wildcard-style matching instead, the LIKE lesson covers pattern matching with % and _. For more on why a single NULL can quietly take out an entire calculation, see the NULL handling lesson. Once the syntax is comfortable, the practice problems have string-cleaning exercises worth running against real data instead of the examples here.