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)