You are viewing an older version of this section. View current production version.
Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

REPLACE

Replaces all occurrences of a substring by another string.

Syntax

REPLACE(string, old_string, new_string);

Arguments

  • string: any string
  • old_string: the string to be replaced
  • new_string: the replacement string

Remarks

  • MemSQL uses case-sensitive match to search for the new_string

Return Type

String

Examples

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)