Back to Function Index

REPLACE()

Overview

REPLACE() replaces each occurrence of the search string in the input string with the corresponding replace string. This function is case sensitive, meaning it will only match and replace exact character sequences.

Function Format

REPLACE(string, search string, replace string)

Return Value

REPLACE() returns a character string

Examples

// Replacing words
REPLACE("APPLES AND ORANGES", "ORANGES", "APPLES") = "APPLES AND APPLES"
REPLACE("APPLES AND ORANGES", "APPLES", "ORANGES") = "ORANGES AND ORANGES"

// Replacing substrings
REPLACE("APPLES AND ORANGES", "ES", "E") = "APPLE AND ORANGE"

// Case sensitivity examples
REPLACE("Apples and Oranges", "apples", "Pears") = "Apples and Oranges"  // No match due to case
REPLACE("Apples and Oranges", "Apples", "Pears") = "Pears and Oranges"   // Exact match

Notes

• Important behaviors:
  • Case sensitive matching
  • Replaces all occurrences of the search string
  • Returns original string if search string is not found
  • Empty search string returns original string
  • Empty replace string effectively removes the search string
• Common use cases include:
  • Text substitution
  • String cleaning
  • Data normalization
  • Format standardization
  • Pattern removal
• The function is useful for:
  • Data transformation
  • String manipulation
  • Text processing
  • Data cleaning
• Considerations:
  • Case sensitivity must be handled explicitly
  • Multiple replacements are performed sequentially
  • Search string can be any length
  • Replace string can be any length

See Also

• REGEXP()
• TRANSLATE()
• TRIM()
• LOWER()