REPLACE

Substitute every literal occurrence of a search substring with a replacement string.

Category: stringReturns: VARCHARDialect: Standard

Syntax

REPLACE(str, search, replacement)

Description

## Overview Scans the input string and replaces every non-overlapping occurrence of the search substring with the replacement string. Unlike REGEXP_REPLACE, REPLACE treats both arguments as literal text, not regular expressions. It is the safe and fast choice when the search pattern is a known fixed string. Typical uses include stripping formatting characters before casting a string to a number, swapping delimiters, masking a fixed token, and normalising inconsistent spellings. ## Behavior - Returns NULL when any argument is NULL. - The search is case-sensitive. - An empty search string returns the original string unchanged. - An empty replacement string effectively deletes every occurrence of the search substring. - Matching is greedy left-to-right, non-overlapping: after a match is replaced, scanning continues after the replacement, not inside it. - Operates on UTF-8 code points; multi-byte characters are matched correctly. - No regex metacharacters are interpreted. Parentheses, dots, and backslashes are treated literally. ## Compatibility - REPLACE is widely supported across SQL dialects with identical semantics. - For pattern-based replacement use REGEXP_REPLACE. - For character-by-character substitution use TRANSLATE.

Parameters

Name	Type	Description
`str`		Specifies the input string in which replacements are performed.
`search`		Specifies the literal substring to find. The match is case-sensitive. This is not a regular expression.
`replacement`		Specifies the string to substitute for each occurrence of the search substring. Pass an empty string to delete the search substring.

Examples

-- Simple replacement
SELECT REPLACE('hello world', 'world', 'there');  -- 'hello there'

-- Every occurrence is replaced
SELECT REPLACE('aaa', 'a', 'bb');  -- 'bbbbbb'

-- Strip a substring by replacing it with empty
SELECT REPLACE('(555) 123-4567', ' ', '');  -- '(555)123-4567'

-- Case-sensitive match (no replacement made)
SELECT REPLACE('Hello', 'hello', 'hi');  -- 'Hello'

-- NULL propagates
SELECT REPLACE(CAST(NULL AS VARCHAR), 'a', 'b');  -- NULL

-- Sanitize a column by removing commas
SELECT REPLACE(raw_amount, ',', '') AS amount_clean
FROM ops.etl.staging_invoices;

Pitfalls

REPLACE is not regex. Characters like '.', '*', '+' are matched literally, which is usually what you want; if you need pattern matching use REGEXP_REPLACE.
Matching is case-sensitive. REPLACE('Hello', 'hello', 'hi') returns the string unchanged. Lowercase both sides, or use REGEXP_REPLACE with the 'i' flag.
Chained REPLACE calls to strip multiple characters are quadratic in the number of characters. For more than a few characters, prefer TRANSLATE or a single REGEXP_REPLACE.
An empty search argument returns the string unchanged rather than raising an error. Validate inputs if you intended to loop and strip.