MySQL REPLACE() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL REPLACE() Function

The REPLACE() function in MySQL is used to replace all occurrences of a substring within a string with a new substring.

Syntax

REPLACE(string, substring, new_string)

Parameters

Parameter Description
string Required. The original string in which the replacement will occur.
substring Required. The substring that you want to replace.
new_string Required. The new substring that will replace the old one.

Definition and Usage

  • Purpose: The REPLACE() function performs a case-sensitive replacement of all occurrences of a specified substring within a given string.
  • Note: This function is case-sensitive, meaning it will only replace substrings with the exact case provided.

Usage Examples

Example 1: Basic String Replacement

Replace the substring "SQL" with "HTML" in the string "SQL Tutorial":

SELECT REPLACE("SQL Tutorial", "SQL", "HTML");

Output:

REPLACE("SQL Tutorial", "SQL", "HTML")
HTML Tutorial

Explanation: "SQL" is replaced with "HTML".

Example 2: Replace Multiple Occurrences

Replace "X" with "M" in the string "XYZ FGH XYZ":

SELECT REPLACE("XYZ FGH XYZ", "X", "M");

Output:

REPLACE("XYZ FGH XYZ", "X", "M")
MYZ FGH MYZ

Explanation: All occurrences of "X" are replaced with "M".

Example 3: Case-Sensitive Replacement

Replace "X" with "m" in the string "XYZ FGH XYZ":

SELECT REPLACE("XYZ FGH XYZ", "X", "m");

Output:

REPLACE("XYZ FGH XYZ", "X", "m")
mYZ FGH mYZ

Explanation: "X" is replaced with "m", but the "Y" remains unchanged because the replacement is case-sensitive.

Example 4: Case-Sensitive Replacement with Lowercase

Replace "x" with "m" in the string "XYZ FGH XYZ":

SELECT REPLACE("XYZ FGH XYZ", "x", "m");

Output:

REPLACE("XYZ FGH XYZ", "x", "m")
XYZ FGH XYZ

Explanation: No replacements occur because the lowercase "x" does not match the uppercase "X" in the original string.

Technical Details

  • Works In: From MySQL 4.0 onwards.
  • Case Sensitivity: The REPLACE() function is case-sensitive, meaning that it distinguishes between uppercase and lowercase characters.
  • Return Type: The function returns a new string with the specified replacements made.
  • Behavior: If the substring is not found, the original string is returned unchanged. If either the substring or the new string is empty, the result will depend on the input values.

Applications

  1. Text Normalization: Use REPLACE() to normalize or standardize text by replacing certain terms or characters throughout a string.
  2. Data Correction: Replace incorrect or outdated information in text data, like correcting typos or abbreviations.
  3. Format Changes: Replace specific formats, like changing date or address formats within stored text.

Example Use Case

If you have an e-commerce database where product names need to be updated, you could use REPLACE() to remove unwanted words. For instance, replacing all occurrences of "OldModel" with "NewModel":

UPDATE Products
SET ProductName = REPLACE(ProductName, 'OldModel', 'NewModel');

This would update the product names in the Products table.

Key Notes

  • Performance Considerations: Be mindful of the string size and the number of replacements if you are working with large datasets, as REPLACE() processes each occurrence in the string.
  • Case Sensitivity: Since the function is case-sensitive, it is important to consider the case of both the substring and new_string parameters for accurate replacements.

The REPLACE() function is a powerful tool for text manipulation in MySQL, enabling efficient string replacement in both simple and complex scenarios.

Was this article helpful?