How to UPDATE and REPLACE Part of a String in MariaDB
Last Updated :
13 Feb, 2024
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:
OutputExplanation: 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:
OutputExplanation: 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', '[email protected]');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', '[email protected]');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', '[email protected]');
Output:
OutputExample 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:
OutputExplanation: 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:
OutputExplanation: 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:
OutputExplanation: 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.
Similar Reads
How to UPDATE and REPLACE Part of a String in SQLite
In SQLite, updating and replacing parts of a string can be a common task, especially when dealing with textual data. SQLite, serverless architecture offers various methods to solve this problem. In this article, We will learn about string replace in a query with the help of various methods to know h
4 min read
How to UPDATE and REPLACE Part of a String in PL/SQL?
PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. It is developed by Oracle and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language e
3 min read
How to UPDATE and REPLACE Part of a String in SQL Server
In SQLServer, efficient manipulation of strings is crucial for managing databases effectively. Among the fundamental operations are updating and replacing parts of strings within tables. These operations are invaluable for correcting data inconsistencies, enhancing data quality, and transforming tex
4 min read
How to Replace Part of a String in MySQL?
To replace a part of a string in MySQL we use the REPLACE function. MySQL provides this method to manipulate string data in the tables. In this article, we are going to see how we can update a part of the string with some other value in MySQL. Understanding this can enable string manipulation in a m
4 min read
How to replace a portion of strings with another value in JavaScript ?
In JavaScript, replacing a portion of strings with another value involves using the `replace()` method or regular expressions. This process is essential for text manipulation tasks like formatting, sanitization, or dynamic content generation in web development and data processing applications. We ca
3 min read
How to Use str_replace in R?
str_replace() is used to replace the given string with a particular value in R Programming Language. It is available in stringr library, so we have to load this library. Syntax: str_replace( "replacing string", "replaced string") where, replacing string is the string to be replacedreplaced string is
2 min read
How to replace String in PHP ?
Replacing a string in PHP involves substituting parts of a string with another string. Common methods include str_replace() for simple replacements, preg_replace() for pattern-based replacements, substr_replace() for positional replacements, and str_ireplace() for case-insensitive replacements. Each
3 min read
How to replace multiple characters in a string in PHP ?
A string is a sequence of characters enclosed within single or double quotes. A string can also be looped through and modifications can be made to replace a particular sequence of characters in it. In this article, we will see how to replace multiple characters in a string in PHP. Using the str_repl
3 min read
How to Rename a Column Name in MariaDB?
MariaDB is an open-source relational database management system that can be used to create databases, and tables and to query the tables. Anyone who knows how to write SQL queries can use any of the databases like MariaDB or Mysql for the execution of SQL queries. In this article, we will learn abou
4 min read
How to search and replace text in a file in Python ?
In this article, we will learn how we can replace text in a file using python. Method 1: Searching and replacing text without using any external module Let see how we can search and replace text in a text file. First, we create a text file in which we want to search and replace text. Let this file b
5 min read