MySQL Replace String Function Example 1

MySQL REPLACE Function

The MySQL REPLACE function is useful to replace the existing string expression with a new substring value. The basic syntax of string Replace in MySQL is as shown below:

SELECT REPLACE (Expression, Change_String, Replace_String)
FROM Source
  • Expression: Please specify the valid expression on which you want to perform the search. It updates a portion of a string present here.
  • Change_String: Specify any valid string (or character) you want to change. Whatever you place here, It will substitute by Replace_String
  • Replace_String: Please specify the valid new one or characters that you want to insert into String_Expression.

For this MySQL String REPLACE demonstration, We are going to use the below shown data.

MySQL String Replace Function Example

MySQL Replace String Example

This method is used to alter the characters in the original text or sentence with the specified expression. The below query shows various ways to use this function.

SELECT REPLACE('Tutorial Gateway', 'Tutorial', 'MySQL');

SELECT REPLACE('www.tutorialgateway.org', 'org', 'net');

-- Let me Try with Integer values
SELECT REPLACE('123456', 34, 75);

SELECT REPLACE(123456, 234, 379);

-- Testing NULLS
SELECT REPLACE('Tutorial', 'Tu', NULL);
MySQL Replace String Function Example 1

In this example, we implement the replace function on different columns present in the employee table. The following Function code change the Developer in Department Name column with Engineer and com in Email with net

USE mysqltutorial;
SELECT FirstName,
	   LastName,
       DepartmentName,
       REPLACE(DepartmentName, 'Developer', 'Engineer') AS `Replaced Dept`,
       Email,
       REPLACE(Email, 'com', 'net') AS `Replaced Email`
FROM employe;

From the below MySQL screenshot you can see the result.

MySQL Replace String Function Example 2