string
REPLACE
Replaces every occurrence of from_str inside source with to_str. Case-sensitive in Postgres and SQLite; MySQL follows the column's collation.
REPLACE(source, from_str, to_str)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| REGEXP_REPLACE | Pattern-based replacement (Postgres, MySQL 8, external extension in SQLite) |
| TRANSLATE(str, from, to) | Postgres: single-char-for-char replacement in one pass |
| 'g' flag | REGEXP_REPLACE global flag to replace every match (Postgres) |
Examples
SELECT REPLACE('foo-bar-baz', '-', '_');Returns 'foo_bar_baz'
UPDATE users SET email = REPLACE(email, '@old.com', '@new.com');Mass-migrate email domains
SELECT REPLACE('aaaa', 'a', '');Returns empty string — REPLACE strips every match
SELECT REGEXP_REPLACE('abc123def', '[0-9]+', '#');Regex-driven replacement returning 'abc#def'
Dialect notes / Gotcha
REPLACE(NULL, ...) returns NULL — wrap with COALESCE if you need pass-through. Not regex — use REGEXP_REPLACE for patterns. Note: REGEXP_REPLACE is Postgres + MySQL 8+ only. SQLite has no built-in regex functions — install the pcre/ICU extension.
Related functions
SUBSTRING
Extracts a substring by 1-based start position and optional length. All three dialects support the comma-argument form; Postgres also accepts the SQL-standard FROM/FOR clauses and POSIX regex extraction.
CONCAT
Joins two or more strings end-to-end and returns a single string. CONCAT is portable across MySQL, Postgres 9.1+, and SQLite 3.44+; the standard || operator works in Postgres and SQLite but not MySQL unless PIPES_AS_CONCAT is set.
TRIM
Removes matching characters from the start, end, or both sides of a string. Defaults to stripping spaces when no character set is supplied.
LENGTH / CHAR_LENGTH
Returns the number of characters in a string (CHAR_LENGTH) or the number of bytes (LENGTH in MySQL). Postgres and SQLite LENGTH return character count for text, matching CHAR_LENGTH.
LOWER
Converts a string to lowercase using the database's active collation/locale. Universally supported and safe for normalizing case-insensitive lookups.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet