Skip to main content

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

  • SingleStoreDB Cloud uses case-sensitive match to search for the old_string

Return Type

String

Examples

The example below replaces a string in the name column of the company table using the REPLACE function.

CREATE TABLE rep_company (name varchar(30));
INSERT INTO rep_company values ("Zumiez Inc."), ("Orange.com"), ("Pomegranate Corp."), ("FruitCompany Consolidated");
SELECT name FROM rep_company WHERE name LIKE "Zumiez Inc.";
****
+------------------------------+
| NAME                         |
+------------------------------+
| Zumiez Inc.                  |
+------------------------------+
1 row in set (242 ms)

SELECT REPLACE(name,'Inc.','Corp.') FROM rep_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.

CREATE TABLE rep_Emp (Name varchar(50), City varchar(50));
INSERT INTO rep_Emp values("Adam", "Chicago");
SELECT * FROM rep_Emp;
****
+-------------------------------------------------------------+
| Name                         | City                         |
+-------------------------------------------------------------+
| Adam                         | Chicago                      |
+-------------------------------------------------------------+
1 row in set (1.05 sec)

SELECT GROUP_CONCAT(Name, '-', City) FROM rep_Emp;
****
+------------------------------+
| GROUP_CONCAT(Name, '-', Ci...|
+------------------------------+
| Adam-Chicago                 |
+------------------------------+
1 row in set (991 ms)

SELECT GROUP_CONCAT(REPLACE(Name, 'Ad', 'S'),'-', City) FROM rep_Emp;
****
+------------------------------+
| GROUP_CONCAT(REPLACE(Name,...|
+------------------------------+
| Sam-Chicago                  |
+------------------------------+
1 row in set (960 ms)
In this section: