REPLACE()
SQL · String Functions
Theory
REPLACE(string, old, new) returns a copy of string where every occurrence of old is replaced with new. REPLACE('98290-12345', '-', '') removes the dash and returns '9829012345'. All occurrences are replaced, not just the first, and if old is not found the string comes back unchanged. Use an empty string as new to delete text.
REPLACE is available under the same name in almost every database (SQLite, MySQL, Postgres, SQL Server, Oracle, Snowflake). It matches exact substrings, and the match is case-sensitive in most databases, so REPLACE(x, 'it', 'IT') will not touch 'It'. To replace several different things, nest the calls: REPLACE(REPLACE(phone, '-', ''), ' ', '') removes both dashes and spaces. For pattern-based replacement (any digit, any run of spaces), many databases offer REGEXP_REPLACE. Used in an UPDATE, REPLACE can clean a whole column in one statement.
Why we use it
Imported data is full of unwanted characters and outdated values: dashes and spaces in phone numbers, old domain names, wrong abbreviations. REPLACE fixes them in bulk without writing any code outside SQL.
When to use it
Use REPLACE to remove or swap a fixed piece of text. Nest calls for a few different pieces. For single-character mappings use TRANSLATE where available; for patterns use REGEXP_REPLACE.
How it works
- Take the string, the old text and the new text
- Search for the next occurrence of old
- Found an occurrence? Yes: Swap it for new and keep searching after it. No: Stop searching.
- Nested REPLACE runs the inner call first
- Return the cleaned string
Syntax
REPLACE(string, old, new)Example
SELECT REPLACE(email, '@techtown.in', '@company.com') FROM employees;Key points
- Replaces ALL occurrences, not just the first
- Case-sensitive in most databases
- Use '' as the new value to delete text
- Nest calls to replace several different substrings
- For patterns use REGEXP_REPLACE where available