Mysql - REPLACE() Function
The `REPLACE()` function in MySQL is used to replace all occurrences of a specified substring within a string with another substring. This function is useful for modifying string values by replacing parts of them with new values.
Syntax
REPLACE(string, search_string, replace_string)
Parameters:
- string: The original string where you want to perform the replacement.
- search_string: The substring to search for within the string.
- replace_string: The substring to replace the search_string with.
How It Works:
- If search_string is found within the string, it is replaced with replace_string.
- If search_string is not found, the string remains unchanged.
- This function is case-sensitive.
Example 1: Basic Usage
SELECT REPLACE('Hello World', 'World', 'MySQL');
Output:
Hello MySQL
Example 2: Replacing Part of a Column Value
Let's consider an example using the `REPLACE()` function with an `employees` table. The `REPLACE()` function replaces all occurrences of a specified string with another string.
| id | first_name | last_name | position |
|---|---|---|---|
| 1 | John | Doe | Software Engineer |
| 2 | Jane | Smith | Project Manager |
| 3 | Emily | Johnson | Business Analyst |
| 4 | Michael | Brown | Software Engineer |
| 5 | Jessica | Williams | Product Manager |
Now, if we want to replace the term "Manager" with "Lead" in the `position` column.
SELECT id, first_name, last_name, position, REPLACE(position, 'Manager', 'Lead') AS new_position FROM employees;
This query will return:
| id | first_name | last_name | position | new_position |
|---|---|---|---|---|
| 1 | John | Doe | Software Engineer | Software Engineer |
| 2 | Jane | Smith | Project Manager | Project Lead |
| 3 | Emily | Johnson | Business Analyst | Business Analyst |
| 4 | Michael | Brown | Software Engineer | Software Engineer |
| 5 | Jessica | Williams | Product Manager | Product Lead |
The `REPLACE()` function replaces "Manager" with "Lead" in the `position` column.
Terms of Use About Us Privacy Policy
All Rights Reserved.