Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT REPLACE('2025-01-31', '-', '/') AS slashed;   -- 2025/01/31

Try it

SQL Replacing, removing and no-match cases

Press Run (or Ctrl + Enter) to see the result here.

SQL Cleaning product names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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') is ba.
  • Not recursive. The replacement text is not scanned again, so REPLACE('aab', 'ab', 'b') is ab, not b. Repeat the call for repeated cleanup.
  • Several replacements are chained: REPLACE(REPLACE(phone, '-', ''), ' ', ''). For single-character swaps PostgreSQL and Oracle have TRANSLATE().
  • Pattern replacement (digits, classes, anchors) needs a regular-expression function: REGEXP_REPLACE in PostgreSQL, MySQL 8 and Oracle. SQLite has none unless you load an extension.
  • Update with care. Preview first with a SELECT of old and new values, then run UPDATE 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) is 1934), but most other databases refuse or need a CAST.