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 w3wiki.
Query:
SELECT REPLACE("Hello World!", "World", "w3wiki") as Greeting;
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 w3wiki. In the output we assigned the alias Greeting is Hello w3wiki! 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:
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:
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.
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.