Examples of Updating Columns by Replacing Substringsin MariaDB

We will ccreatea table and aaddsome records data to the table.

CREATE TABLE EMPLOYEE 
(
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

Let’s add some records to the table.

Query:

INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'clark@some.com');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'dave@some.com');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'ava@some.com');

Output:

Example 1: Updating Column with REPLACE

We will use REPLACE with the UPDATE Clause to replace the email from ‘some.com‘ to ‘domain.net‘.

Query:

UPDATE EMPLOYEE SET email=REPLACE(email, 'some.com', 'domain.net');

Output:

Explanation: In the above query we haveupdatede the email column for each employee has been updated from some.com to domain.net. With the help of Rthe EPLACE function the occurrences of some. arecom’in the email column is replaced with domain.net.

Example 2: Updating Column with REGEXP_REPLACE Function

We will use the REGEXP_REPLACE function to replace a part of the string and then update the string. Let’s replace all the occurrences of a or e in the name column with z using the regex pattern

Query:

UPDATE EMPLOYEE SET name=REGEXP_REPLACE(name, 'a|e','z');

Output:

Explanation: in the above query, We have replaced the character a or e with z as we see in the output image.

Example 3: Updating Column with SUBSTRING and CONCAT

We will use SUBSTRING and CONCAT functions to replace parts of the string. We will update the email to have only 2 characters pr to the ‘@’ symbol and 2 characters after the symbol.

Query:

UPDATE EMPLOYEE SET email=CONCAT(SUBSTRING(email, 1, 2), SUBSTRING(email, LOCATE('@', email), 3));

Output:

Explanation: As we can see the email column has been updated and the new email is just 2 characters before and after the symbol.

MariaDB is one of the most popular open-source database systems. It is developed by the developers of MySQL. In this article, we will How to UPDATE and REPLACE part of a string in MariaDB along with various examples and methods and so on.