MySQL Replace String Function

MySQL REPLACE String Function

The REPLACE() function in MySQL is used to search for a specified substring in a string and replace it with another. It is commonly used for updating text data in database columns.

Syntax:

REPLACE(string, from_substring, to_substring)

• string: The original string where replacements will be made.
• from_substring: The substring that needs to be replaced.
• to_substring: The new substring that will replace from_substring.

Example 1: Basic Usage

Replace the word "world" with "MySQL" in a string:

SELECT REPLACE('Hello world!', 'world', 'MySQL') AS result;

Output:

+---------------+
| result        |
+---------------+
| Hello MySQL!  |
+---------------+

Example 2: Using REPLACE with Table Data

Suppose you have a table called employees with a column address containing values like:

+----+-------------------+
| id | address          |
+----+-------------------+
| 1  | New York, USA    |
| 2  | Los Angeles, USA |
| 3  | Chicago, USA     |
+----+-------------------+

To replace "USA" with "United States" in all records:

UPDATE employees
SET address = REPLACE(address, 'USA', 'United States');

Updated Data:

+----+------------------------+
| id | address               |
+----+------------------------+
| 1  | New York, United States |
| 2  | Los Angeles, United States |
| 3  | Chicago, United States |
+----+------------------------+

Example 3: Case Sensitivity

The REPLACE() function is case-sensitive. If you try:

SELECT REPLACE('Hello World!', 'world', 'MySQL');

It will return "Hello World!" (unchanged) because "world" does not match "World" exactly.

To perform a case-insensitive replacement, use LOWER() or UPPER():

SELECT REPLACE(LOWER('Hello World!'), 'world', 'MySQL');

Output:

+---------------+
| result        |
+---------------+
| hello MySQL!  |
+---------------+

Example 4: Removing Unwanted Characters

If you want to remove unwanted characters from a string (e.g., replace spaces with nothing):

SELECT REPLACE('123-456-7890', '-', '') AS cleaned_number;

Output:

+----------------+
| cleaned_number |
+----------------+
| 1234567890     |
+----------------+

Conclusion

• REPLACE() is useful for modifying text data in MySQL.
• It is case-sensitive.
• It can be used in SELECT and UPDATE statements.
• It does not modify the original string unless used in an UPDATE query.

Would you like more examples of this function? 😊