REPLACE

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

REPLACE(string, old_string, new_string);

Arguments

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

Remarks

SingleStore 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.

SQL

CREATE TABLE rep_company (name varchar(30));
INSERT INTO rep_company values ("Zumiez Inc."), ("Orange.com"), ("Pomegranate Corp."), ("FruitCompany Consolidated");

SQL

SELECT name FROM rep_company WHERE name LIKE "Zumiez Inc.";

+------------------------------+
| NAME                         |
+------------------------------+
| Zumiez Inc.                  |
+------------------------------+
1 row in set (242 ms)

SQL

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.

SQL

CREATE TABLE rep_Emp (Name varchar(50), City varchar(50));
INSERT INTO rep_Emp values("Adam", "Chicago");

SQL

SELECT * FROM rep_Emp;

+-------------------------------------------------------------+
| Name                         | City                         |
+-------------------------------------------------------------+
| Adam                         | Chicago                      |
+-------------------------------------------------------------+
1 row in set (1.05 sec)

SQL

SELECT GROUP_CONCAT(Name, '-', City) FROM rep_Emp;

+------------------------------+
| GROUP_CONCAT(Name, '-', Ci...|
+------------------------------+
| Adam-Chicago                 |
+------------------------------+
1 row in set (991 ms)

SQL

SELECT GROUP_CONCAT(REPLACE(Name, 'Ad', 'S'),'-', City) FROM rep_Emp;

+------------------------------+
| GROUP_CONCAT(REPLACE(Name,...|
+------------------------------+
| Sam-Chicago                  |
+------------------------------+
1 row in set (960 ms)