SQL Reference ยท Scalar Functions
SQL REPLACE() function
Reference for the SQL REPLACE() function: substitute every occurrence of a substring, case sensitivity, NULL and empty-string behavior, dialect differences.
REPLACE(s, find, replacement) returns a copy of s in which every occurrence of find is swapped for replacement. It is used to clean data (remove dashes from phone numbers), to rename parts of values in a SELECT, and, combined with LENGTH(), to count how often something appears.
REPLACE changes only the returned value. To change stored data, use it in an UPDATE.
SELECT REPLACE('2025-01-31', '-', '/') AS slashed; -- 2025/01/31
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
REPLACE(string, find, replacement)
| Name | Description |
|---|---|
string |
The original text. |
find |
The substring to look for. Matching is exact. |
replacement |
What to put in its place. Use '' to delete the matches. |
Returns: the new text. If find does not occur, the original is returned unchanged. NULL if any argument is NULL (Oracle differs, see below).
Dialect differences
| Database | Behavior |
|---|---|
| SQLite | REPLACE(s, find, repl); case-sensitive; empty find returns s unchanged. |
| PostgreSQL | Same, but it expects text: cast numbers first, REPLACE(n::text, ...). Case-sensitive. |
| MySQL | Same; case sensitivity follows the collation, which is case-insensitive by default for most text columns. |
| SQL Server | Same; case sensitivity follows the collation (default is case-insensitive). |
| Oracle | REPLACE(s, find [, repl]); if repl is omitted or NULL, matches are removed. Empty strings count as NULL. |
-- Oracle: remove all dashes SELECT REPLACE('555-01-23', '-') FROM dual; -- 5550123 -- PostgreSQL: replace several single characters at once SELECT TRANSLATE('555-01 23', '- ', '__'); -- 555_01_23
Usage notes
- All occurrences are replaced, scanned from left to right, without overlapping.
REPLACE('aaa', 'aa', 'b')isba. - Not recursive. The replacement text is not scanned again, so
REPLACE('aab', 'ab', 'b')isab, notb. Repeat the call for repeated cleanup. - Several replacements are chained:
REPLACE(REPLACE(phone, '-', ''), ' ', ''). For single-character swaps PostgreSQL and Oracle haveTRANSLATE(). - Pattern replacement (digits, classes, anchors) needs a regular-expression function:
REGEXP_REPLACEin PostgreSQL, MySQL 8 and Oracle. SQLite has none unless you load an extension. - Update with care. Preview first with a
SELECTof old and new values, then runUPDATE products SET name = REPLACE(name, ...) WHERE name LIKE '%...%'. - Count occurrences:
(LENGTH(s) - LENGTH(REPLACE(s, x, ''))) / LENGTH(x). - Numbers are converted to text in SQLite (
REPLACE(1234, 2, 9)is1934), but most other databases refuse or need aCAST.