Important
Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.
REPLACE
On this page
(See REPLACE for the DML command.
The REPLACE() function 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 Helios 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: