REPLACE SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

Replace all occurrences of a substring within a string with a new substring.

REPLACE(str, from, to) returns str with all occurrences of 'from' replaced by 'to'. It is non-destructive and differs from REGEXP_REPLACE, which uses regular expressions for pattern matching. Note that REPLACE INTO is a separate DML statement for upsert operations (deletes and inserts on unique key conflicts), not related to this string function.

Compatibility

SQL REPLACE Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	all	REPLACE(str, from_str, to_str). Case-sensitive (regardless of collation). Replaces all occurrences.
PostgreSQL	Native	all	REPLACE(str, from, to). Case-sensitive. For case-insensitive replace, use REGEXP_REPLACE with the 'i' flag.
SQL Server	Native	all	REPLACE(str, old, new). Case sensitivity depends on the database collation. In a case-insensitive collation, REPLACE('Hello', 'hello', 'X') returns 'X'.
Oracle	Native	all	REPLACE(str, search [, replacement]). If replacement is omitted or NULL, all occurrences of search are deleted. Case-sensitive.
SQLite	Native	all	REPLACE(str, old, new). Case-sensitive. Replaces all occurrences.

Details

All five engines support REPLACE with the same three-argument signature. Case sensitivity is the key difference: MySQL is always case-sensitive, SQL Server follows collation, PostgreSQL is always case-sensitive (use REGEXP_REPLACE with 'i' flag for case-insensitive). Oracle's optional third argument (deletion mode) is unique.

Standard Syntax

SELECT REPLACE(column, 'old_value', 'new_value') FROM tbl;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

REPLACE(str, from_str, to_str). Case-sensitive (regardless of collation). Replaces all occurrences.

SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL' SELECT REPLACE('aabbcc', 'b', 'x'); -- 'aaxxcc'

PostgreSQL Native syntax

REPLACE(str, from, to). Case-sensitive. For case-insensitive replace, use REGEXP_REPLACE with the 'i' flag.

SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL' SELECT REGEXP_REPLACE('Hello World', 'world', 'SQL', 'i'); -- case-insensitive

SQL Server Native syntax

REPLACE(str, old, new). Case sensitivity depends on the database collation. In a case-insensitive collation, REPLACE('Hello', 'hello', 'X') returns 'X'.

SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL' SELECT REPLACE('aabbcc', 'b', 'x'); -- 'aaxxcc'

Oracle Native syntax

REPLACE(str, search [, replacement]). If replacement is omitted or NULL, all occurrences of search are deleted. Case-sensitive.

SELECT REPLACE('Hello World', 'World', 'SQL') FROM DUAL; -- 'Hello SQL' SELECT REPLACE('Hello World', 'World') FROM DUAL; -- 'Hello ' (deleted)

SQLite Native syntax

REPLACE(str, old, new). Case-sensitive. Replaces all occurrences.

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