REPLACEFunctions
Buy Me A Coffee

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

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

Per-Database Syntax & Notes

MySQL

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

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

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

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

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

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