Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

REPLACE

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)