How to UPDATE and REPLACE Part of a String in MariaDB

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.

MariaDB REPLACE String Function

The REPLACE function is used to replace all the occurrences of a substring with some other string.

Syntax:

REPLACE(string, old_substring, new_substring)

Explanation:

• string: The string in which to replace.
• old_substring: The substring to be replaced.
• new_substring: The substring to replace the old substring.

Example of REPLACE() Function

The following query replaces World with GeeksforGeeks.

Query:

SELECT REPLACE("Hello World!", "World", "GeeksforGeeks") as Greeting;

Output:

Output

Explanation: In the above query we use the REPLACE function to modify the string Hello World! by replacing every occurrence of the substring World with GeeksforGeeks. In the output we assigned the alias Greeting is Hello GeeksforGeeks! which is a string where the specified substitution has been applied.

MariaDB REGEXP_REPLACE() Function

The REGEXP_REPLACE() function replaces all occurrences of a substring that matches the regex pattern with another string.

Syntax:

REGEXP_REPLACE(string, pattern, new_substring)

Explanation:

• string: It is a string wthatis replace.
• pattern: It is a pattern to match substrings with.
• new_substring: The substring to replace the old substring.

Example

The following query replaces all occurrences of ‘and‘ with ‘or‘.

Query:

SELECT REGEXP_REPLACE('Black and blue and green','and','or') AS output;

Output:

Output

Explanation: In the above query, We have replaced and with or in a string.

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:

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:

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:

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:

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.

Conclusion

Overall After readingthe whole articl,e you havea good understandingof How to UPDATE and REPLACE part of a string in MariaDB, We have seen the variousmethodsd along with the output and explanation of output. Now you can perform various queries to REPLACE part of a string in MariaDB.