REPLACE function in MySQL

Introduction

The REPLACE function helps you change parts of text inside your data easily.

You want to fix a typo in many text entries at once.

You need to update old words to new ones in a list of names.

You want to remove unwanted characters from a text field.

You want to change a part of an address or email in your database.

Syntax

MySQL

REPLACE(original_text, text_to_find, text_to_replace_with)

It looks inside the original_text and replaces all occurrences of text_to_find.

If text_to_find is not found, the original_text stays the same.

Examples

Replaces 'world' with 'friend' in the text.

MySQL

SELECT REPLACE('hello world', 'world', 'friend');

Changes all 'a's to 'o's, resulting in 'bonono'.

MySQL

SELECT REPLACE('banana', 'a', 'o');

No 'x' found, so the text stays 'apple'.

MySQL

SELECT REPLACE('apple', 'x', 'y');

Sample Program

This replaces every 'cats' with 'dogs' in the sentence.

MySQL

SELECT REPLACE('I love cats and cats love me', 'cats', 'dogs') AS result;

Important Notes

REPLACE is case-sensitive, so 'Cats' and 'cats' are different.

It replaces all matches, not just the first one.

Use it carefully when replacing common words to avoid unwanted changes.

Summary

REPLACE changes parts of text inside strings.

It replaces all occurrences of the text you want to change.

If the text to find is missing, the original text stays unchanged.