REPLACE() Function in MySQL

The REPLACE() function in MySQL is a powerful tool for string manipulation, allowing users to substitute specific substrings within a larger string.

This functionality is particularly useful in various applications such as updating text data, cleaning up input or adjusting content in a database. In this article, We will learn about the REPLACE() Function in MySQL by understanding various examples.

REPLACE() Function in MySQL

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

Syntax:

REPLACE(string, from_substring, to_substring)

Parameter:

• string: The original string.
• from_substring: The substring to be replaced.
• to_substring: The substring to replace with.

Examples of REPLACE() Function in MySQL

Example 1

In this example, we will see how we can replace any string value with a new string value by using the replace function. Let's consider an example where "MySQL" replace with "HTML" value.

Below given is the replace function values.

Replace "MySQL" with "HTML"

Now, we will see how we can read the replace values.

SELECT REPLACE("MySQL in Geeksforgeeks", "SQL", "HTML");

Output:

Example 2

Let's update a string by replacing all occurrences of the letter "X" with the letter "A"

Replace "X" with "A"

Now, If we want to read the replaced value then use the following function given below.

SELECT REPLACE("It is Good to study XXX from GFG", "X", "A");

Output:

Example 3

Let's replace all occurrences of the letter "x" in a given string with the letter "a".

Replace "x" with "a"

Now, If we want to read the replaced value then use the following function given below.

SELECT REPLACE("It is Good to study xxx from GFG", "x", "a");

Output:

Conclusion

The REPLACE() function in MySQL is a valuable tool for modifying text strings by replacing specified substrings with new ones. In our example, replacing "x" with "a" in the phrase "It is Good to study xxx from GFG" results in "It is Good to study aaa from GFG." This functionality is essential for tasks such as data cleaning, content updating, and enhancing overall data quality in databases, making it a key component of effective string manipulation.