Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Replaces all occurrences of a substring by another string.
REPLACE(string, old_string, new_string);
new_string
String
The example below replaces a string in the name
column of the company
table using the REPLACE
function.
SELECT name FROM company WHERE name LIKE "Zumiez Inc.";
****
+------------------------------+
| NAME |
+------------------------------+
| Zumiez Inc. |
+------------------------------+
1 row in set (242 ms)
SELECT REPLACE(name,'Inc.','Corp.') FROM company WHERE name LIKE "Zumiez Inc.";
****
+------------------------------+
| REPLACE(name,'Inc.','Corp.') |
+------------------------------+
| Zumiez Corp. |
+------------------------------+
1 row in set (1.34 sec)
The REPLACE
function can also be grouped with other string functions. An example of combining REPLACE
with GROUP_CONCAT
function is shown below.
SELECT * FROM Emp;
****
+-------------------------------------------------------------+
| Name | City |
+-------------------------------------------------------------+
| Adam | Chicago |
+-------------------------------------------------------------+
1 row in set (1.05 sec)
SELECT GROUP_CONCAT(Name, '-', City) FROM Emp;
****
+------------------------------+
| GROUP_CONCAT(Name, '-', Ci...|
+------------------------------+
| Adam-Chicago |
+------------------------------+
1 row in set (991 ms)
SELECT GROUP_CONCAT(REPLACE(Name, 'Ad', 'S'),'-', City) FROM Emp;
****
+------------------------------+
| GROUP_CONCAT(REPLACE(Name,...|
+------------------------------+
| Sam-Chicago |
+------------------------------+
1 row in set (960 ms)