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.
On this page
(See REPLACE for the DML command.
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
-
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.
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.REPLACE
with GROUP_
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)
Last modified: March 5, 2025