Skip to main content

REPLACE function

The REPLACE function is used to replace all occurrences of a substring within a string with another substring. It is commonly used for data cleansing, formatting, and text manipulation operations.

Syntax

REPLACE(string, search_string, replacement_string)

Arguments

  • string: The original string in which replacements will be made

  • search_string: The substring to be replaced

  • replacement_string: The string that will replace all occurrences of search_string

Returns

Returns a new string where all occurrences of search_string in the original string have been replaced with replacement_string.

  • If search_string is not found in string, the original string is returned unchanged .

  • If search_string is an empty string, the original string is returned unchanged.

  • If replacement_string is an empty string, all occurrences of search_string are effectively removed.

Examples

Basic string replacement

-- Example 1: Replace a word in a string
> SELECT REPLACE('Hello World', 'World', 'SQL');
'Hello SQL'

-- Example 2: Replace all occurrences of a character
> SELECT REPLACE('banana', 'a', 'o');
'bonono'

-- Example 3: Remove all occurrences of a substring
> SELECT REPLACE('Hello World', 'o', '');
'Hell Wrld'

-- Example 4: Replacement when search string is not found
> SELECT REPLACE('Hello World', 'xyz', 'abc');
'Hello World'