REPLACE()
Replaces all occurrences of a substring within a string.
Description
REPLACE swaps every occurrence of one substring for another within a string.
It's the go-to for cleaning data — normalizing separators, fixing typos, or
stripping unwanted characters (by replacing them with an empty string).
Syntax
REPLACE(string, from_substring, to_substring)Parameters
| Name | Description | Optional |
|---|---|---|
| string | The text to search within. | No |
| from_substring | The text to find. | No |
| to_substring | The text to insert in its place. | No |
Return Type
Returns a VARCHAR.
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
Delete characters with ''
REPLACE(phone, '-', '') removes all dashes from a phone number.
Common Mistakes
- Replacing only the first match.
REPLACEchanges all occurrences, not just the first; there's no built-in "first only" in standard SQL. - Case sensitivity. Matching is case-sensitive; normalize with
LOWERon both sides if needed. - Confusing with OVERLAY/REGEXP. For pattern-based replacement, reach for a regex function instead.
Related Functions
See also: SUBSTRING, TRIM, REGEXP_REPLACE.