MySQL Replace

Syntax of MySQL REPLACE() Function

The Syntax for the MySQL replace() functions are as follows:

We will look at the meaning of each of these parameters in the next section.

Parameters of MySQL REPLACE() Function

The MySQL REPLACE() function takes three parameters as input:

string: The original string in which we want to replace the search_string.

search_string: The substring that needs to be replaced in the original string.

replace_string: The substring that will replace the search_string.

Return Value of MySQL REPLACE() Function

The MySQL replace() function returns the modified string, where the search_string is replaced with the replace_string.

Exceptions of MySQL Replace() Function

There are no specific exceptions to the MySQL replace() function. The only thing to note here is that the MySQL replace() functions perform case-sensitive modifications.

How does the MySQL REPLACE() Function Work?

To understand the basic understanding of the MySQL replace() function, let's look at an example: Let us assume that you are running a fruit stall and you have a table by the name of inventory. This table contains all the information about the fruits that you have in stock such as fruit type, their quantity, the selling price per kg, and cost price per kg. the inventory table looks something like this.

Table Name: inventory

Now you want to find out all the fruits in your stock whose quantity is at least 40kg and their selling price is at max 160 rupees. We can do that with the help of the SQL query.

This query will go through each row one by one and filter out all the fruits that have a minimum of 40kg in stock but also have a selling price equal to or under 160 rupees. The output for the above query will look something like this.

OUTPUT:

But let us say you don't want to display the private information regarding your inventory to everyone so you decide to encrypt the results a little bit. You want to replace every 'a' in the fruit name in the resulting table with an 'x'. MySQL provides you with a very powerful function, replace(). The MySQL replace() function takes in 3 parameters, the original string, letter, or part of the string that you want to replace, and the letter or string that you want to replace it by. Let us see how we can use the MySQL replace() function in the above example.

In this query, we are using the MySQL replace() function to replace every occurrence of the letter 'a' in our fruit names with an 'x'. We are also using fruit_name as an alias for a better representation of the results. Then we are applying the same conditions to filter out our results.

OUTPUT:

MySQL replace() function takes in a string, a target string, and a replacement string. Then it finds every occurrence of the target string in the original string and replaces it with the replacement string.

Now that we have seen how the MySQL replace() function work, let us look at different examples and different scenarios to understand the concepts a little better.

Examples

Now it's time to dive into some examples and see how we can use this amazing function to our advantage.

Example 1

Let us start with a very simple example of taking a string and replacing a part of it with a different string.

Output:

You must notice that when we run the above query, the name of the result field is the whole Replace function itself along with all the parameters that we passed, you can leave it as it is or you can use an alias to make the search results look a lot cleaner and easier to understand. To learn more about aliases in SQL and how to use them properly, head over to Scaler topics by clicking here and start learning.

Now let us look at the above code while using an alias.

Output:

The name of the field in the final result has been changed to Result, which is much cleaner than the whole replace() function itself as the field name.

Example 2

While the MySQL replace() function is a great tool to search and replace words in your table without altering the actual table, you do need to take note of the case sensitivity. Take a look

Output:

As you can see, Nothing is changed in the final result as MySQL replace() function performs a case-sensitive search. So be careful while using the replace() function.

Example 3

Another property of the MySQL replace() function is that it replaces all occurrences of the target string and not just once.

Let us say you wanted to print a string that looks something like this, "apple is the best" but you made a mistake and the string looks something like this, "epple is the best". Let us use the MySQL replace() method to correct this mistake and replace the first 'e' with an 'a';

Output:

As we must have noticed, the MySQL replace() function did replace the first 'e' with an 'a', but it also replaced all occurrences of the letter 'e' with an 'a'. So you need to be cautious while using the replace() function as it might lead to some unwanted replacements.

Example 4

The MySQL replace functions also work with numeric parameters.

Output:

It can be noticed that the number '2' has been replaced by the number '9'.

Example 5

Now let us look at some more complex examples and see how the replace function can be used with actual tables in our relational databases. We have seen one such example in a previous section, so let us pull up the same inventory table for this example as well.

The inventory table for our fruit shop looks something like this.

Output:

But what if we want to make some changes to the original table itself? We can use the replace function in combination with other SQL statements to achieve this.

Now if we print all the fruits on our table, we will get something like this

Example 6

In this example, we will see how we can use the MySQL replace() function with other MySQL conditional statements such as the WHERE clause. In this example, we are using a products table which contains details of all our products, their supplier id and category id, and much more. The table looks something like this.

Table Name: Products

Let us start with a simple example where we are changing every occurrence of the word 'Chef' in the column ProductName and replacing it with the word 'Master'

Here we are using the UPDATE statement to update our Products table and then set every occurrence of the word 'Chef' to the word 'Master'.

Output:

Now let us use the replace() function in combination with the WHERE clause.

In this example, we are replacing the occurrence of the word 'Chef' with the word 'Master' only for Products whose price is less than 22.00.

Output:

We can also use another field name inside the replace function along with the set statement. Let us look at an example where we are replacing each ProductName with its Unit.

Now if we print the product names, we will get something like this

Output:

As you can see, all the Product names have been replaced with their corresponding Unit.

See Also

With this, we have come to an end to our segment on the MySQL replace function but the learning should never stop. You can also check out these articles at Scaler topics.

• Full SQL tutorial
• SQL using MySQL
• Database management systems

MCQs

1. What does the replace() function in MySQL do?

   A) Deletes a character in a string.

   B) Replaces a specified character or substring with another string in a given string

   C) Returns the length of a string

   D) Replaces the whole string with a new string

Correct Answer: B

2. Select replace("abc Abc", "A", "X");. What will be the result of the following SQL query

   A) abc Abc

   B) abc abc

   C) xbc xbc

   D) abx Xbx

Correct Answer: D

3. Can the REPLACE() function replace multiple occurrences of a substring in a string?

   A) Yes, but only if the occurrences are consecutive

   B) No, it can only replace the first occurrence of a substring in a string

   C) Yes, it can replace all occurrences of a substring in a string

   D) Yes, but at max 3 occurrences

Correct Answer: C

Thank You for reading!