Find and Replace string using MySQL

Sometimes i need to find and replace some strings in a column in MySQL so for this task MySQL uses the replace() function.  This is a string function and the base syntax is as follows. 

select REPLACE('string/column',old_string,new_string);
  • you can pass a string to it or use the entry provided by a column.
Example of using the replace() function in MySQL 1 - Simple string replacement: 
mysql select REPLACE('adrian.oprea','oprea','dba');
+---------------------------------------+
| REPLACE('adrian.oprea','oprea','dba') |
+---------------------------------------+
| adrian.dba                            |
+---------------------------------------+
1 row in set (0,00 sec)
2 - String replacement using the where clause: 
mysql SELECT REPLACE(user, 'r', 'i am r') from user where user='root';
+------------------------------+
| REPLACE(user, 'r', 'i am r') |
+------------------------------+
| i am root                    |
+------------------------------+
6 rows in set (0,00 sec)
Note: -the function is case sensitive, so be careful when using. As you can see bellow nothing was changes. 
mysql SELECT REPLACE('abc abc', 'A', 'B');
+------------------------------+
| REPLACE('abc abc', 'A', 'B') |
+------------------------------+
| abc abc                      |
+------------------------------+
1 row in set (0,00 sec)

Copyright 2020. All Rights Reserved