How to use MySQL REPLACE function in SELECT and UPDATE queries

MySQL REPLACE function replaces all occurrences of a string inside another string.

Basic syntax:

REPLACE(str, from, to)

Parameter	Description
str	The string to be searched. It can be a string literal value or a column name from a table. If it's a column name, all values in the column are searched.
from	The old string that needs to be replaced.
to	The new string to replace.

REPLACE() performs a case-sensitive search and replacement.

Examples

1. Query below replaces all occurrences of the upper case string "GPT" with the string "XXX", but lowercase "gpt" is not replaced because REPLACE() function is case-sensitive.

-- Result: ChatXXX is a smart XXX but is not gpt.
select replace("ChatGPT is a smart GPT but is not gpt.", "GPT","XXX");

2. Query below searches the word "Manager" in column ContactTitle in suppliers table, and replace it with "Managing Director". Note that this is a SELECT query and the actual values in the column are not changed.

select CompanyName, 
       ContactTitle as OldTitle, 
       replace(ContactTitle, "Manager","Managing Director") as NewTitle 
from suppliers
order by SupplierID;

29 records returned:
29 contact titles were replaced in SELECT query

3. Query below updates the word "Manager" to "Managing Director" in ContactTitle column in suppliers table when CompanyName is "Tokyo Traders". Note that this is an UPDATE query and the actual values in the column are changed. Make sure you make a backup of the table before the update.

Update suppliers 
set ContactTitle = replace(ContactTitle, "Manager","Managing Director")
where CompanyName = "Tokyo Traders";

1 record was updated:

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

17. Use LEAST function to find the SMALLEST value from multiple arguments

18. How to make case-sensitive comparison in MySQL

19. Use GREATEST function to find the LARGEST value from multiple arguments

20. MySQL MID() function with examples

21. MySQL LOCATE() function with examples

22. MySQL SUBSTR() function with examples

23. MySQL POSITION() function with examples

Back to Tutorial Index Page

	Custom Search