string

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

Loading playground environment...

Delete characters with ''

REPLACE(phone, '-', '') removes all dashes from a phone number.

Common Mistakes

Replacing only the first match. REPLACE changes all occurrences, not just the first; there's no built-in "first only" in standard SQL.
Case sensitivity. Matching is case-sensitive; normalize with LOWER on both sides if needed.
Confusing with OVERLAY/REGEXP. For pattern-based replacement, reach for a regex function instead.

See also: SUBSTRING, TRIM, REGEXP_REPLACE.